130+ Useful Excel Date Calculations

I often use date calculations in projects that are require dynamic pointers to specific days in a month or week.

Some examples included calculations that
return:

  • Next Monday
  • Last Friday this Month
  • First Thursday this Month
  • Second Saturday Next Month

The list goes on…

It’s useful to take those date calculations and create a Named formula. For instance, you could ‘Define’ a new Name with the calculation that returns the date for Next Monday. Then you can use =Next_Monday each time you need that date. This way, you can store useful date calculations in your workbook and easily call them up when you need them.

 

I’ve included a list of 130+ date formulas that

could prove to be useful for some of you. Hopefully you can use this list as a reference for your date calculation needs.

Feel free to add a comment and tell me a useful date calculation that is missing here. I’ll add it to the list.

 

Find a Monday

Target Date

Formula

Count_Mondays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-1))

First_Monday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Monday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Monday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Monday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Monday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Monday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Monday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Monday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Monday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Monday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Monday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Monday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+2-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(2>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Monday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,6))

Last_Monday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,6))

Last_Monday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),6))

Previous_Occuring_Monday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,7,1,2,3,4,5)

Next_Occuring_Monday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),2,3,4,5,6,7,1)

Monday_This_Week

=TODAY()-WEEKDAY(TODAY(),1)+2

Monday_Next_Week

=TODAY()+7-WEEKDAY(TODAY(),1)+2

Monday_Previous_Week

=TODAY()-7-WEEKDAY(TODAY(),1)+2

 

Find a Tuesday

Target Date

Formula

Count_Tuesdays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-2))

First_Tuesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Tuesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Tuesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Tuesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Tuesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Tuesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Tuesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Tuesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Tuesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Tuesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Tuesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Tuesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(3>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Tuesday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,5))

Last_Tuesday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,5))

Last_Tuesday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),5))

Previous_Occuring_Tuesday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),5,6,7,1,2,3,4)

Next_Occuring_Tuesday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),3,4,5,6,7,1,2)

 

 

Find a Wednesday

Target Date

Formula

Count_Wednesdays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-3))

First_Wednesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Wednesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Wednesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Wednesday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Wednesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Wednesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Wednesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Wednesday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Wednesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Wednesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Wednesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Wednesday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(4>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Wednesday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,4))

Last_Wednesday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,4))

Last_Wednesday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),4))

Previous_Occuring_Wednesday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),4,5,6,7,1,2,3)

Next_Occuring_Wednesday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),4,5,6,7,1,2,3)

 

 

Find a Thursday

Target Date

Formula

Count_Thursdays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-4))

First_Thursday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Thursday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Thursday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Thursday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Thursday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Thursday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Thursday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Thursday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Thursday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Thursday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Thursday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Thursday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+5-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(5>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Thursday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,3))

Last_Thursday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,3))

Last_Thursday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),3))

Previous_Occuring_Thursday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),3,4,5,6,7,1,2)

Next_Occuring_Thursday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),5,6,7,1,2,3,4)

 

 

Find a Friday

Target Date

Formula

Count_Fridays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-5))

First_Friday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Friday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Friday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Friday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Friday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Friday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Friday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Friday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Friday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Friday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Friday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Friday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+6-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(6>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Friday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,2))

Last_Friday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,2))

Last_Friday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),2))

Previous_Occuring_Friday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,4,5,6,7,1)

Next_Occuring_Friday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),6,7,1,2,3,4,5)

Friday_This_Week

=TODAY()-WEEKDAY(TODAY(),1)+6

Friday_Next_Week

=TODAY()+7-WEEKDAY(TODAY(),1)+6

Friday_Previous_Week

=TODAY()-7-WEEKDAY(TODAY(),1)+6

 

 

Find a Saturday

Target Date

Formula

Count_Saturdays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-6))

First_Saturday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Saturday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Saturday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Saturday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Saturday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Saturday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Saturday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Saturday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Saturday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Saturday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Saturday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Saturday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(7>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Saturday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))

Last_Saturday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,1))

Last_Saturday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Previous_Occuring_Saturday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),1, 2,3,4,5,6,7)

Next_Occuring_Saturday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),7, 1, 2,3,4,5,6)

 

 

Find a Sunday

Target Date

Formula

Count_Sundays_This_Month

=4+(DAY(TODAY()-DAY(TODAY())+35)<WEEKDAY(TODAY()-DAY(TODAY())-7))

First_Sunday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(1-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Second_Sunday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(2-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Third_Sunday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

Fourth_Sunday_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(4-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7

First_Sunday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(1-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Second_Sunday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(2-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Third_Sunday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(3-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

Fourth_Sunday_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))+(4-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))))*7

First_Sunday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(1-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Second_Sunday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(2-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Third_Sunday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(3-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Fourth_Sunday_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))+(4-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))))*7

Last_Sunday_Of_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,7))

Last_Sunday_Of_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,7))

Last_Sunday_Of_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),7))

Previous_Occuring_Sunday

=TODAY()-CHOOSE(WEEKDAY(TODAY()),7, 1, 2,3,4,5,6)

Next_Occuring_Sunday

=TODAY()+CHOOSE(WEEKDAY(TODAY()),1, 2,3,4,5,6,7)

 

 

Find Other Dates

Target Date

Formula

Last_Day_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

OR

=EOMONTH(TODAY(),0)

Last_Day_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)

OR

=EOMONTH(TODAY(),1)

Last_Day_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

OR

=EOMONTH(TODAY(),-1)

First_Day_This_Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)+1

First_Day_Next_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)+1

First_Day_Previous_Month

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,0)+1

New Year’s Day

=DATE(YEAR(TODAY()),1,1)

Martin Luther King Jr. Day

=DATE(YEAR(TODAY()),1,1)+14+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),1,1)),1,0,6,5,4,3,2)

President’s Day

=DATE(YEAR(TODAY()),2,1)+14+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),2,1)),1,0,6,5,4,3,2)

Memorial Day

=DATE(YEAR(TODAY()),5,31)-CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),5,31)),6,0,1,2,3,4,5)

Independence Day

=DATE(YEAR(TODAY()),7,4)

Labor Day

=DATE(YEAR(TODAY()),9,1)+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),9,1)),1,0,6,5,4,3,2)

Columbus Day

=DATE(YEAR(TODAY()),10,1)+7+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),10,1)),1,0,6,5,4,3,2)

Veteran’s Day

=DATE(YEAR(TODAY()),11,11)

Thanksgiving Day

=DATE(YEAR(TODAY()),11,1)+21+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),11,1)),4,3,2,1,0,6,5)

Christmas Day

=DATE(YEAR(TODAY()),12,25)

Easter
(from John Walkenbach)

Be sure to retype the double quotes after you paste into Excel.

=DOLLAR((“4/”&YEAR(TODAY()))/7+MOD(19*MOD(YEAR(TODAY()),19)-7,30)*14%,)*7-6

 

 

21 thoughts on “130+ Useful Excel Date Calculations

  1. jeff weir

    Oh, and you missed Next_International_Spreadsheet_Day and Jeffs_Next_Birthday. Other than that, it looks pretty comprehensive to me.

  2. MF

    What a comprehensive list! I have to bookmark this page for future reference. 🙂 Thanks for sharing.

    btw, I prefer EOMONTH to find month-end dates. 🙂

  3. datapig Post author

    Thanks John! I added that mysterious formula from your site. I have no idea how it works, but it seems to work a treat.

  4. Merrill Jackson

    Another way to calculate Easter’s Date:

    Public Function EasterDate(Yr As Integer) As Date

    Dim d As Integer
    d = (((255 – 11 * (Yr Mod 19)) – 21) Mod 30) + 21
    EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 – ((Yr + Yr \ 4 + d + (d > 48) + 1) Mod 7)

    End Function

  5. Heather

    Wow! I’ll definitely bookmark this site – but I’m with MF, I prefer:
    EOMONTH(TODAY(),0) for end of this month
    EOMONTH(TODAY(),-1) for end of last month
    EOMONTH(TODYA(),1) for end of next month

    And then by adding +1 you get beginning of next month, this month, and month after next respectively.

  6. sam

    @Jeff
    Next_Friday_The_Thirteen

    =TODAY()+MATCH(“135″,DAY(ROW(INDIRECT(TODAY()&”:”&TODAY()+(365*2))))&WEEKDAY(ROW(INDIRECT(TODAY()&”:”&TODAY()+(365*2))),2),0)-1

  7. GMF

    Since you create them as named ranges, do you place these in a book.xlt template so they’re available in new workbooks, or do you have a macro to create them when you need them for existing workbooks as well?

  8. jeff weir

    Nice one, Sam. I’m pretty sure you could probably do one for ‘end of days’, but I’m afraid to ask…

  9. Dermot

    That Easter one doesn’t work for me, until I edited it slightly, as follows:

    =DOLLAR((4&”/”&YEAR(TODAY()))/7+MOD(19*MOD(YEAR(TODAY()),19)-7,30)*14%,)*7-6

    i.e., I removed the 4 at the beginning from the double quotes, and added an ampersand, then it worked perfectly. I’m using Excel 2010, is that why the original didn’t work?

  10. datapig Post author

    Dermot: Looks like the double quotes get wonky when pasting the formula into Excel. I’ve added a note to retype the double quotes after pasting.

    Thanks!

  11. Julian Chen

    I found the formulas of Next_Occuring_(days of week) were not correct. each of them should add 6 respectively to get the right dates.

  12. Pingback: Save Time With Excel Date Calculations

Leave a Reply

Your email address will not be published. Required fields are marked *