How do I create a week ending date formula for an expense report?

Let’s say you are putting together an expense report sheet for all of your employees to use. You want them to be able to enter a week ending date and the spreadsheet will automatically compute the rest of the dates for the week. Here are the two main formulas we will be using:

=IF(B1<>"",IF(WEEKDAY(B1)<>7,B1+7-WEEKDAY(B1),B1),"")

=IF($B$2<>"",$B$2-6,"")

First it says, if the date in cell B1 does not equal (< >) blank (“”), then go on to the next part of the formula, otherwise leave blank. The formula =weekday() returns a number 1 to 7 identifying the day of the week of date. In our example, I have chosen 2/12/2010 as the week ending date. =weekday(B1) turns out to be 6. So, if the weekday of B1, which is 6, does not equal 7, then take the value of B1, 12, add 7 to get 19, then subtract weekday of B1, 6. We end up at 13. (recap: 12+7=19. 19-6=13.) The date displayed is 2/13/2010.

Now it is on to the next formulas for each day. Sunday’s formula is: =IF($B$2<>"",$B$2-6,""). If B2 does not equal blank then subtract 6, otherwise leave blank. In our example we have 13-6=7 which gives us the correct date for Sunday, February 7, 2010. The formula for Monday is: =IF($B$2<>"",$B$2-5,"") which gives us 13-7=8 for Feb. 8th and so on until Saturday. There you have it, one easy way to improve your expense reports.

I could just put this up as templates and not even bother explaining how they work but how many people would take the time to try and figure out how they work? You will learn a lot and feel much more accomplished if you try building these spreadsheets on your own. Feel free to let me know if you have any questions or comments.