3.1 Add/Subtract month from a given date

Very often, you will have business problems where you have to add or subtract month from a given date. One scenario is calculation for EMI Date. Say, you have a date of 11/15/23 (MM/DD/YY) in A1 and you want to add number of months which is contained in Cell B1.

The formula in this case would be

  =EDATE(A1,B1)
  [Secondary formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) ]

Now, you want to subtract month which is contained in Cell B1.

  =EDATE(A1,-B1)
  [Secondary formula =DATE(YEAR(A1),MONTH(A1)-B1,DAY(A1)) ]

3.2 Add/Subtract year from a given date

In many business problems, you might encounter situations where you will need to add or subtract years from a given date.
Let's say A1 contains Date and B1 contains numbers of years.
If you want to add Years to a given date, formulas would be

  =EDATE(A1,12*B1)
  =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

If you want to subtract Years from a given date, formulas would be

  =EDATE(A1,-12*B1)
  =DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))

3.3 Convert a number to a month name

Use below formula to generate named 3 lettered month like Jan, Feb....Dec

  =TEXT(A1*30,"mmm")

Replace "mmm" with "mmmm" to generate full name of the month like January, February....December in any of the formulas in this post.

3.4 Convert date to a calendar quarter

Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is 1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4.

  =CEILING(MONTH(A1)/3,1)
  OR
  = ROUNDUP(MONTH(A1)/3,0)
  OR
  =CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3, 4,4,4)

3.5 Convert date to a indian financial year quarter

Assuming date is in Cell A1. You want to convert it into a Indian Financial Year Quarter. Jan to Mar is 4, Apr to Jun is 1, Jul to Sep is 2 and Oct to Dec is 3.

  =CEILING(MONTH(A1)/3,1)+IF(MONTH(A1)<=3,3,-1)
  OR
  =ROUNDUP(MONTH(A1)/3,0)+IF(MONTH(A1)<=3,3,-1)
  OR
  =CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)

3.6 Calculate age from given Birthday

=DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months
"&DATEDIF(A1,TODAY(),"md")&" Days"

3.7 Number to date format conversion

If you have numbers like 010216 and you want to convert this to date format, then the following formula can be used

=--TEXT(A1,"00\/00\/00") for 2 digits year

Note – Minimum 5 digits are needed for above formula to work
If you have numbers like 01022016 and you want to convert this to date format, then the following formula can be used.

=--TEXT(A1,"00\/00\/0000") for 4 digits year

Note – Minimum 7 digits are needed for above formula to work

3.8 Number to time format conversion

If you have numbers like 1215 and you want to convert this to hh:mm format, then the following formula can be used

=--TEXT(A1,"00\:00")

Note – Minimum 3 digits are needed for above formula to work To convert to hh:mm:ss format

=--TEXT(A1,"00\:00\:00")

Note – Minimum 5 digits are needed for above formula to work

3.9 First day of month for a given date

Suppose you have been given a date say 11/15/23 (MM/DD/YY) and you want to calculate the first day of the Current Month. Hence, you want to achieve a result of 11/1/2023 (MM/DD/YY).
The formulas to be used

=DATE(YEAR(A1),MONTH(A1),1)
=A1-DAY(A1)+1
=EOMONTH(A1,-1)+1

3.10 Count any day of the week between 2 dates (e.g Monday)

Suppose A1 = 23-Jan-23 and A2 = 10-Nov-23. To find number of Mondays between these two dates

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))

“Mon” can be replaced with any other day of the week as per need.

3.11 Find next week of a given day

There are 2 scenarios in this case. For example, if today’s date is 2-Jan-17 (Monday) and I try to find the next Monday, I can get either 2-Jan-17 or 9-Jan-17 as per need. For Tuesday to Sunday, it is not a problem as they come after 2-Jan-17 only.

Case 1 - If the Day falls on the same date, then that very date (Hence, in case of 2-Jan-
17, next Monday would be 2-Jan-17 only)
Next Mon =CEILING($A$1-2,7)+2
Next Tue =CEILING($A$1-3,7)+3
Next Wed =CEILING($A$1-4,7)+4
Next Thu =CEILING($A$1-5,7)+5
Next Fri =CEILING($A$1-6,7)+6
Next Sat =CEILING($A$1-7,7)+7
Next Sun =CEILING($A$1-8,7)+8

Case 2 - If the Day falls on the same date, then next date (Hence, in case of 2-Jan-17, next
Monday would be 9-Jan-17 only)
Next Mon =CEILING($A$1-1,7)+2
Next Tue =CEILING($A$1-2,7)+3
Next Wed =CEILING($A$1-3,7)+4
Next Thu =CEILING($A$1-4,7)+5
Next Fri =CEILING($A$1-5,7)+6
Next Sat =CEILING($A$1-6,7)+7
Next Sun =CEILING($A$1-7,7)+8

3.12 Find previous week of a given day

There are 2 scenarios in this case. For example, if today’s date is 2-Jan-17 (Monday) and I try to find the previous Monday, I can get either 2-Jan-17 or 26-Dec-16 as per need. For Tuesday to Sunday, it is not a problem as they come prior to 2-Jan-17 only.

Case 1 - If the Day falls on the same date, then that very date (Hence, in case of 2-Jan-
17, previous Monday would be 2-Jan-17 only)
Previous Mon =CEILING($A$1-8,7)+2
Previous Tue =CEILING($A$1-9,7)+3
Previous Wed =CEILING($A$1-10,7)+4
Previous Thu =CEILING($A$1-11,7)+5
Previous Fri =CEILING($A$1-12,7)+6
Previous Sat =CEILING($A$1-13,7)+7
Previous Sun =CEILING($A$1-14,7)+8

Case 2 - If the Day falls on the same date, then previous date (Hence, in case of 2-Jan-
17, previous Monday would be 26-Dec-16 only)
Previous Mon =CEILING($A$1-9,7)+2
Previous Tue =CEILING($A$1-10,7)+3
Previous Wed =CEILING($A$1-11,7)+4
Previous Thu =CEILING($A$1-12,7)+5
Previous Fri =CEILING($A$1-13,7)+6
Previous Sat =CEILING($A$1-14,7)+7
Previous Sun =CEILING($A$1-15,7)+8

3.13 Find last day of month for a given date

Suppose, you are given a date say 10/22/23 (MM/DD/YY) and we want to have the last date of the month for the given date. Hence, you needs an answer of 10/31/23. The formulas to be used in this case

=EOMONTH(A1,0)
=DATE(YEAR(A1),MONTH(A1)+1,0)
=DATE(YEAR(A1),MONTH(A1)+1,1)-1

3.14 Find number of days in a month

Suppose, you have been given a date say 15-Nov-23 and you have to determine how many days this particular month contains.
The formula which you need to use in the above case would be

=DAY(EOMONTH(A1,0))

Explanation - EOMONTH(A1,0) gives the last date of the month and DAY function extract that particular Day from the last date of the month.

3.15 Check if given Year is a Leap Year

Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following formula

=MONTH(DATE(A1,2,29))=2

TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.

3.16 Last working day of the month for a given date

If A1 holds a date, the formula for calculating last Working Day of the month would be

=WORKDAY(EOMONTH(A1,0)+1,-1)

The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula

=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.
You also have option to give a range which has holidays. In that case, your formula would

=WORKDAY(EOMONTH(A1,0)+1,-1,D1:D10)
=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

3.17 First working day of the month for a given date

If A1 contains a date, then formula for First Working Day of the month would be

=WORKDAY(EOMONTH(A1,-1),1)

The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.
You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH(A1,-1),1,D1:D10)
=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

3.18 Date for Nth Day of a given Year

Suppose A1 contains the Year and you are asked to find 69th day of the year which is contained in A2. Then formula for finding Nth day of the year would be

=DATE(A1,1,1)+A2-1

3.19 Extract Date and Time from Date Timestamp

Suppose you have a date timestamp value in cell A1 - A1 = 06/14/23 10:15 PM
And you want to extract date and time out of this.
To extract date, use following formula and format the result cell as date

= INT(A1)

To extract time, use following formula and format the result cell as time

= MOD(A1,1)

3.20 Get Financial Year

A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-23), the financial year is 2022-23 (It is also written as FY23). On 1-Apr-23, it will become 2022- 23 (It is also written as FY23).
Now if a date is given, then following formula can be used to derive 2022-23 kind of result.

=YEAR(A1)-(MONTH(A1)<=3)&"-"&YEAR(A1)+(MONTH(A1)>3)

To generate FY16 kind of result, following formula can be used

="FY"&RIGHT(YEAR(A1)+(MONTH(A1)>3),2)

3.21 First working day of the year

If a year is given in A1 say 2022, below formula can be used to know the first working day of the year (format the result as date)

=WORKDAY(EOMONTH("1JAN"&A1,-1),1)

The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula

=WORKDAY.INTL(EOMONTH("1JAN"&A1,-1),1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.
You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH("1JAN"&A1,-1),1,D1:D10)
=WORKDAY.INTL(EOMONTH("1JAN"&A1,-1),1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

3.22 Last working day of the year

If a year is given in A1 say 2022, below formula can be used to know the last working day of the year (format the result as date)

=WORKDAY("1JAN"&A1+1,-1)

The above formula assumes that your weekends are Saturday and Sunday.
But, if your weekends are different (e.g. in gulf countries), you can use following formula

=WORKDAY.INTL("1JAN"&A1+1,-1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.
You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY("1JAN"&A1+1,-1,D1:D10)
=WORKDAY.INTL("1JAN"&A1+1,-1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

3.23 Convert Gregorian Date to Julian Date

Q. First what is a Julian Date?
A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems.
7 Digits - YYYYDDD - 2022092 (This is 1-Apr-2022. 92 means that this is 92nd day from 1- Jan in that year)
5 Digits - YYDDD - 22092
Q. What formulas to use to convert Gregorian Dates to Julian Dates?
A. For 7 Digits, use following formula

=TEXT(A1,"yyyy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

=TEXT(A1,"yy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

3.24 Convert Julian Date to Gregorian Date

For 7 Digits Julian Dates, following formula should be used

=DATE(LEFT(A1,4),1,RIGHT(A1,3))

For 5 Digits Julian Dates, following formula should be used depending upon which century (Note - Julian dates are most likely to fall into 20th Century)

21st Century
=DATE(20&LEFT(A1,2),1,RIGHT(A1,3))

20th Century
=DATE(19&LEFT(A1,2),1,RIGHT(A1,3))

Note - 19 or 20 can be replaced with some IF condition to put in right 19 or 20 depending upon the year. For example, year 82 is more likely to be in 20th century where year 15 is more likely to be in 21st century.