Always Return the Previous Saturday's Date Using NOW Function in MS Excel 2007

4.9k views Asked by At

I have a workbook that I am using the NOW function to return today's date and then I have another cell to convert to WEEKNUM that I use to MATCH on with a different tab. I need to be able to create a formula to read today's date and always return the previous Saturday. Example: Today is 6-8-2015. I want this formula to return 6-6-2015. Any ideas on how to make this work. I am struggling here.

1

There are 1 answers

1
Peter On BEST ANSWER

How about: =NOW()-(WEEKDAY(NOW(),1))

This will return the time 'now' minus the number of days from Saturday where Sunday = 1, Monday = 2 etc.

Note, this does include the timestamp along with the day (eg. 6/6/2015 14:48). If you want just the date you could daisy-chain a text function around it.