7 Date Formulas that make life easy

At times managing dates can be tricky. Here are 7 Date Formulas and tips to make your life super easy

1. How to find the exact age ?
2. How to find the last Friday & Second Saturday of the month ?
3. How to find the Quarter number and Indian Financial Year Quarter ?
4. How to convert dates (as text) from mm-dd-yy format to dd-mm-yy format ?
5. How to calculate the Project End Date ?
6. How to calculate Project Days ?
7. Cool Bonus Tips on Dates

 

1. Find the Exact Age

Assume that you have the Birth Dates of a few people. This formula can help you find the exact age of the person

=DATEDIF(DOB,TODAY(),”y”)&” Yrs “&DATEDIF(DOB,TODAY(),”ym”)&” Months “&DATEDIF(DOB,TODAY(),”md”)&” Days”

Couple of things to note

1. DOB is the Date of Birth
2. If you have not heard about the DATEDIF function, let me tell you that it exists but is not documented for in Excel Functions. It comes from the older versions (I guess Excel 2003) of Excel but you can’t use it unless you know its syntax. Excel provides no help around it. If you want to learn it, here is DATEDIF for you
3. This formula will automatically calculate the exact age as on today’s date

 

2. Last Friday & Second Saturday of the Month

 — > Last Friday

 --> Second Saturday

At times you have the need to find the last Friday of the month or even the second Saturday of the month. Here are 2 formulas that will help you do that

• Formula for Last Friday of the Month =SUM((WEEKDAY(EOMONTH(Date,0)-{0;1;2;3;4;5;6},2)=5)*(EOMONTH(Date,0)-{0;1;2;3;4;5;6}))
• Formula for Second Saturday of the Month =SUM((WEEKDAY((EOMONTH(Date,-1)+1)+{0;1;2;3;4;5;6},2)=6)*((EOMONTH(Date,-1)+1)+{0;1;2;3;4;5;6}))+7

Couple of things to note

1. Date is any date of the month
2. These are array formulas. In simple terms, when you are done typing the formula in the cell, you need to press CTRL+SHIFT+ENTER for this formula to run

 

3. Find the Quarter number and Indian Financial Year Quarter

A lot of people ask me how to find the quarter number?

The problem gets interesting when you are required to customize the quarter number as per your own Financial Year, for instance the Indian Financial Year.

Let’s get on top of both of these

Formula to find the calendar year quarter =”Q”&CEILING(MONTH(Date),3)/3

Date is any date of the month

Now let’s see how can we tweak the above formula to find Indian Financial Quarter. Just for understanding, here is how the Indian FY quarters work

• Apr – Jun is Q1
• Jul – Sep is Q2
• Oct – Dec is Q3
• Jan – Mar is Q4

Formula to find Indian FY Qtr =”Q”&CEILING(MONTH(EDATE(Date,-$F$19)),3)/3

Note that

1. Date is again any Date of the month
2. Cell $F$19 is month ending for Indian Financial Year (i.e. = 3). You can customize this as per your need

A further step to this problem could be to add the Financial Year along with the Qtr Number

Here is the formula =”FY “&IF(MONTH(Date)<=$F$19,RIGHT(YEAR(Date)-1,2)&”-“&RIGHT(YEAR(Date),2),RIGHT(YEAR(Date),2)&”-“&RIGHT(YEAR(Date)+1,2))&” Q”&CEILING(MONTH(EDATE(Date,-$F$19)),3)/3

Note that

1. Date is again any Date of the month
2. Cell $F$19 is month ending for Indian Financial Year (i.e. = 3). You can customize this as per your need

 

4. Changing MM-DD-YY text format to DD-MM-YY format

Different people read and write dates differently. Worst happens when the dates are stored as text in MM-DD-YY format and you have to change it to DD-MM-YY format

Here is the formula to change dates into dd-mm-yy format =DATEVALUE(MID(Date,SEARCH(“/”,Date)+1,2)&”/”&LEFT(Date,SEARCH(“/”,Date)-1)&”/”&RIGHT(Date,2))

Note that Date is any date

 

5. Calculating Project End Date

Assume that you have the following information

1. Project Start Date
2. Working Days to finish the project
3. Saturdays and Sundays are off (as weekends)
4. List of Holidays when you’ll not be working

and you need to calculate on which date will you finish the project ?

Here is a simple formula that helps you take care of all those conditions =WORKDAY.INTL(Project Start, Days, Weekend Pattern, List of Holidays)

A few things to note

1. You probably can understand what does project start and days mean easily
2. The WORKDAY.INTL (available in Excel 2010 and above) formula also gives you the option to choose the weekend pattern. It automatically populates a drop down as soon as you reach the 3rd part of the formula
   • 
   • So if you choose 1 it means Sat & Sun, 7 means Fri & Sat
   • By default your weekend pattern will be 1 (i.e. Sat and Sun)
3. List of holidays is simply the range of cells which contain holiday dates. Just in case your holidays are clashing with the weekends, excel will take care of those situations automatically 🙂
4. Excel 2007 has WORKDAY Function which is exactly same as WORKDAY.INTL but you don’t get the option to choose the weekend. Sat and Sun are considered as default weekend

 

6. Calculating Project Days

The situation could be reverse of what we just saw i.e. you can have the following information available

1. Project Start Date
2. Project End Date
3. Saturdays and Sundays are off (as weekends)
4. List of Holidays when you’ll not be working

and you need to calculate on how many days will it take to finish the project ?

The formula to calculate the number of days is =NETWORKDAYS.INTL(Project Start, Project End, Weekend Pattern, List of Holidays)

A few things to note

1. The only thing that changes here is that instead of Days you have Project End Date
2. Rest everything remains the same as =WORKDAY.INTL function
3. Excel 2007 has NETWORKDAYS Function where you don’t have the option to choose the weekends. Sat and Sun are considered as default weekend

 

7. Cool Bonus Tips on Dates

Here are 3 cool tips that you can keep handy while working with dates

 

 

More tips to help you become productive

1. 4 Interesting Date Formats
2. Learn how IF, Nested IF, AND & BOOLEAN logic works in Excel
3. 10 Excel habits that you must develop
4. Learn Cell Referencing for writing Robust Excel Formulas 

 

What kind of problems do you face while working with dates?

In the comments section, take a moment to write down the problems that you face while working with dates. I’ll try and knock down as many problems as I can. Cheers!