Basic Date Arithmetic

You would have come across data with dates and sometimes you would have needed to get a corresponding date or day after a certain period of time. For example, a confirmation date after 6 months or a date post payment terms of 90 days or post an ageing schedule for 6 months.

Please refer this post for Date basics

We shall take up this exercise in a common template which is given below. It will take just a few seconds to type in.

Case 1) Employee 007 has joined your organization on 01-May-2013. We need to find his date of confirmation after a period of six months {in the cell D2}

An easier but more approximate method would be directly add the number of days in D2.

6 months ~ 180 days

^Syntax = D2 = C2 + 180

However, it’s an approximation as each of the months succeeding May-2013 would not have exactly 30 days.

^*If the cell is not in a format that displays a date, you can right-click on the cell and format cell as date.

(Refer this post in case of any doubts)

 

Case 2) 2a) Alternatively, we can use a formula called EDATE, which would correctly add up the six following months after 01-May-2013

^Syntax = EDATE(Start Date or Input Date, Number of Months)

Use of EDATE with inputs as previous cell and 6 months gives the desired result.

So, Mr. Bond should have been confirmed on 01-Nov-2013, we may need to check out if it’s a working day or not. You would not like to give a confirmation letter to Mr. Bond on a Sunday, right?

2b) Let’s find out the day of the week for that purpose.

^Syntax = WEEKDAY(Input Date or D2, Return type usually as 1)

• For our calendar & context, we use 1 as the return type since our week usually starts on a Sunday (=1) and ends on a Saturday (=7).
• Now when we get the result, it pops out as a serial number i.e 6 in this case.
• For that we need an already familiar function called TEXT() (Described in this post) with the required parameters, which would be the WEEKDAY input and formatting for returning the full day name i.e “dddd” (more than 3 d’s, 3 d’s will return a short day name, 2 d’s the serial number)

2c) DATEDIFFERENCE This case refers to a situation in which need to find the time for which James Bond has served your organization.

There is a MS-undocumented function which historically is borrowed by Microsoft from a company called, well it’s Lotus (now owned by IBM). So, it will never show the arguments for an input although a small popup surfaces, when you type DATEDIF(

 

^Syntax = DATEDIF(Start Date, End Date, Unit of Formatting)

Units of Formatting

^DF.Syntax Y – Number of compete Years

^DF.Syntax M – Number of compete Months

^DF.Syntax D – Number of Days

^DF.Syntax YM – Number of complete Months after subtracting the completed years. (< 12) (Days not summing to 1 month are ignored)

^DF.Syntax MD – Number of Days after subtracting the completed months & years

^DF.Syntax YD – Number of Days after subtracting the completed years (months included)

If you take the start date of our James Bond, and dynamic function - TODAY()  as the end date taking the M argument gives the following result.

Exercise

Now applying the same principles can you get an output of Total Experience like the one in the bottom-right cell ? (You may do a search on text operators)

Have a nice weekend folks!