Date Calculation
I have an Excel Worksheet that contains a date in Column A. This date "Begin Date" is always EQ to the first date of a given month (i.e. 04/01/14, 11/01/13, etc.) In Column B, I need to calculate the "Expiration Date" which is 13 months after the Begin Date and always the 6th Weekday of the month. For example, A1=04/01/14. Cell B1 should output 05/08/15 (the 6th Weekday of May). A second example would be, A1=10/01/14. Cell B1 should output 11/09/15 (the 6th Weekday of November). 
June 4th, 2015 10:16am

=EDATE(A1, 13) + IF(OR(WEEKDAY(EDATE(A1, 13)) = 7,  WEEKDAY(EDATE(A1, 13)) = 1), 9, 7)

This should do the trick.  If the date + 13 months is a Saturday or Sunday, it will always be the 10th of the month else it is the 8th.  This is based on the default WEEKDAY setting of Sunday = 1 and Saturday = 7

Regards

Free Windows Admin Tool Kit Click here and download it now
June 4th, 2015 3:55pm

I may be missing something, but when I use the formula provided, it doesn't calc properly. For example, in A1 "Begin Date" the value is 10/01/14, then B1 should calculate 11/09/15 which is the 6th Weekday. The formula is returning 11/10/15 which is the 7th Weekday.

Any suggestions?

June 4th, 2015 4:30pm

=EDATE(A1, 13) + IF(WEEKDAY(EDATE(A1, 13)) = 7, 9, IF( WEEKDAY(EDATE(A1, 13)) = 1, 8, 7))

Apologies, the above is the correct formula.  I calculated Sunday and Saturday to have the same days until the 6th working day, Saturday has one more day than Sunday.


Free Windows Admin Tool Kit Click here and download it now
June 4th, 2015 5:00pm

It works!!!

You have made my day. Thank you very much for the assistance.

June 4th, 2015 5:19pm

=EDATE(A1, 13) + IF(WEEKDAY(EDATE(A1, 13)) = 7, 9, IF( WEEKDAY(EDATE(A1, 13)) = 1, 8, 7))

Apologies, the above is the correct formula.  I calculated Sunday and Saturday to have the same days until the 6th working day, Saturday has one more day than Sunday.


Free Windows Admin Tool Kit Click here and download it now
June 4th, 2015 8:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics