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 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

 

 

Excel Power BI Boot Camp in October

It’s time for another crass commercial message. Bill Jelen and I put on a Power BI Boot Camp back in May of this year. That event was sold out and lots of folks asked us to hold another one this year.

Well, I wanted to give you a heads up that another BI training event is scheduled for October this year in Dallas Texas.

This 3-day event is aimed squarely at Excel analysts who find it increasingly necessary to become more efficient at working with the new Microsoft BI tools like Power Pivot and Power Query.  Get a guided tour of the mysterious world of Microsoft’s new BI Tools from a business analyst’s point of view, introducing you to the rich set of tools and reporting capabilities that can be leveraged to more effectively synthesize data into Business Intelligence Dashboards.

 Learn how to:

 * Analyze large amounts of data and report those results in a meaningful way
 * Get better visibility into data with new Data Mining tools
 * Perform advanced Clustering and Market Basket analytics
 * Add interactive controls to your dashboards without VBA
 * Automate repetitive data cleansing and transformation tasks
 * Create eye-catching visualizations and Dashboards
 * Create map-based dashboards
 * Access external data sources to expand your message.   

Register before August 31 to get a $100 discount off registration.

Click Here to Register for this Event

Quick Tip: Extract Embedded Workbooks from PowerPoint

Ever need to get the embedded workbooks out of PowerPoint slides?

You know…those workbooks that hide behind the tables and charts in a PowerPoint presentation?

It’s easy…

Simply right-click on the embedded object and select Worksheet ObjectàOpen.

Once the workbook is opened, you can save it to a new location.

It’s amazing what hides behind that right-click sometimes.

Bonus Tip:

Want to extract all the images from a PowerPoint document at once?

You could right-click on each image and choose the Save As option.

But doing that for dozens of images totally blows.

An alternative method is to crack open the document and drag them out of there. Follow these steps:

  1. Save your PowerPoint document as a pptx file.
  2. Close your PowerPoint document and add .zip extension to the end of the file name.
  3. Open the zip file and double-click on the ppt folder and then double click on the media folder.
  4. Drag any or all media files you see to your PC. You’ll even be able to extract any embedded videos that may be in the PowerPoint document.

     

  5. Close the zip file and remove the .zip extension from the file name.

     

    Well…I guess that’s it for this week. Have a great weekend!

Excel Dashboard Color Scheme Selector

I recently stumbled upon Josh Tapley’s nifty Color Picker for Xcelsius dashboards. In his post, Josh says:

“Many times I have seen themes that I like on Adobe’s Kuler and ColourLovers.com only to find out that they don’t translate well to graph format.”

I completely agree! Being one who’s more than willing to steal brilliant ideas, I thought I’d try replicating Josh’s color picker in Excel.

I created an Excel workbook that allows you to cycle through a set of color schemes to find one that suits your dashboard. Once you have one you like, simply note the RGB values in cells M2:M6, or click the Capture Colors button to have the colors added to your Recently Used Colors panel.

ColorScheme

I even added a table where you can edit the color schemes with stolen your own HEX color combinations.

I’m sure there is a way to programmatically create a new theme based on the chosen colors, but I’m too lazy to figure that out right now.

I left all the code in the workbook open for your reading pleasure.

Feel free to download the Color Scheme Selector workbook.

Happy coloring!

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 3

This is Part 3 of a project where I attempt to create a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. So far, I was able to create a macro that outputs Cell Referenced GETPIVOTDATA formulas for the target pivot table. My last objective is to see if I can bring the Page Fields along for the ride. That is to say, I would like to auto-create slicers to interactively alter the values returned by the GETPIVOTDATA formulas.

The screen capture below shows how Page Filters can be converted to Slicers that work with the auto-generated GETPIVOTDATA formulas.

ConvertPivovtToFormuasl2

You can try it for yourself a sample file.

Download Sample File

All the code for the macro is documented in the sample file, so I won’t bore you with the details. I will say that the tricky part of dynamically creating slicers is avoiding duplicates. Excel seems to freak out if you create a duplicate slicer. So each time the macro is run, we need to check for existing slicers. If existing slicers exists, we copy the slicer and point the copy to our pivot table. Otherwise we can create a new slicer with no issues.

By the way, I need to mention that I made heavy use of the excellent set of articles from Jan Karel Pieterse to get a head start on programming slicers. Check out Jan Karel’s site for all kinds of useful brain candy.

Well that’s it for this little side project of mine.

I would say that this macro has moderate usability. If you need a dashboard model fast, you can create a pivot table, run this macro, and voila! You’ve got yourself a set of formulas that can be moved, copied, and adjusted to create an interactive reporting model pretty quickly.

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 2

Last week, I embarked on a journey to see if I could build a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. I was able to create a macro that outputs hardcoded GETPIVOTDATA formulas for the target pivot table.

In this post, I’ll show you a macro that improves the output to include cell references instead of hard-coded values. With this macro, you can point to a pivot table and fire the code to get a new sheet with a formula driven version of the pivot. Read more

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 1

One of the cool things about PowerPivot and other OLAP pivot tables is that you can convert the entire pivot table into a series of CUBE formulas. With just a single click, Excel will replace your PivotTable with formulas that call back to the Data Model. Converting your pivot tables into formulas basically gives you a pivot table that can be taken apart. You can do things like insert rows and columns, add your own calculations between data items, combine the data with other data on a dashboard, and you can modify the report in all sorts of ways by simply moving the formulas around. Read more

Excel 2016 Function Changes

Hi there! I know it’s been a while since my last post. I’ve been updating several books for the Office 2016 release. I thought I’d pop up and provide at least a glimmer of useful info. I’ll attempt to get back to regular blogging in the upcoming weeks.

Since I’m knee-deep in updating content on formulas, I’d like to share a few Function changes (as far as I can tell) introduced with Excel 2016.

New Excel Functions in 2016

As far as I can tell, only 5 new functions will be introduced in Excel 2016.

These forecasting functions are no doubt included as a necessary part of the new Forecast Sheet functionality.

You’ll find these functions in the Statistical formula category. Read more

Understanding Office 365 Plans

To my dismay, I’m constantly being asked which version of Office 365 is the best for Excel analysts. Like everyone else, I typically provide a semi-vague answer which was most certainly pulled from some website somewhere. Well, I recently stumbled on this nifty TechNet site that outlines the differences between the various versions of Office 365.

.

Unlike the generic comparisons we typically see, Read more

Too Black for Excel

You know what bothers me? As you can tell from the ambiguously racist title of this post…black font.

I know it sounds strange, but the default font in Excel is somehow too black. The contrast of the white background and the really dark black font color annoys me. In a time when we’ve moved away from the standard VB colors, why does Excel default to RGB(0, 0, 0)?

.

Lately, I’ve made it a point to tone down the default black color in my spreadsheets. Although the difference is subtle, I think my reports look better both on the screen and in print form.

.

For example, in the screenshot below, Read more