Thursday, May 26, 2011

yogi_Calculate A Date X Days From A Fixed Date Skipping Sundays

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
maggyk said:
I'm designing a spreadsheet that records employee work hours. Pay periods run from the 2nd business day prior to the 1st and 16th of the month through the 3rd business day prior to the 1st/16th. (e.g., the first pay period of this month started on Wednesday, April 27th and ended on the Wednesday, May 11th. The last pay period of this month started on Thursday, May 12th and ended on Thursday, May 26th.) I was soooo close setting up reference cells in column A of 1/1/11, 1/16/11, 2/1/11, etc., and then calculating the end of a pay period in Column C with =workday(A2,-3) and the beginning of the next pay period in Column B (next row) with =workday(C2,1). Unfortunately, it turns out this organization works on Saturdays, so I need to be able to return a value if it falls on a Saturday, but skip Sundays and return the date of the following Monday. Any suggestions for a calculation that will help?
here we go maggyk ... have a look at the following solution and see if this would do.