| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtract # of days from date, but if not sat, goto previous sat?
Need to calculate dates by subtracting a certain number of days, but if it is
not a particular day of the week, it needs to go back to the previous week and give me the date of that particular day of the week. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtract # of days from date, but if not sat, goto previous sat?
something like:
=IF(WEEKDAY(TODAY()-B9,1) = 3,TODAY()-B9,TODAY()-B9-(WEEKDAY(TODAY()-B9))-(7-3)) B9 contains the number of days to subtract Where 3 represents Tuesday. Change to suit. -- Regards, Tom Ogilvy "Fernando" wrote: Need to calculate dates by subtracting a certain number of days, but if it is not a particular day of the week, it needs to go back to the previous week and give me the date of that particular day of the week. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtract # of days from date, but if not sat, goto previous sa
Tom,
The formula works, but in some cases it would go back an extra week. For example I have to go back 28 days from 10/10/06 and make sure that it is a Sunday. If you subtract 28 days to Oct 10, you end up at Tue Sept 12th. If you have to go back to the closest Sunday, then the formula should give you Sun Sept 10th, but it is giving me Sun Sept 03. The funny thing is that works for some days, but for other do not work. Can you help me? Fernando "Tom Ogilvy" wrote: something like: =IF(WEEKDAY(TODAY()-B9,1) = 3,TODAY()-B9,TODAY()-B9-(WEEKDAY(TODAY()-B9))-(7-3)) B9 contains the number of days to subtract Where 3 represents Tuesday. Change to suit. -- Regards, Tom Ogilvy "Fernando" wrote: Need to calculate dates by subtracting a certain number of days, but if it is not a particular day of the week, it needs to go back to the previous week and give me the date of that particular day of the week. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtract # of days from date, but if not sat, goto previous sat?
Hi Fernando, try this formula, again B9 is the number of days to subtract but the *2* represents Tuesday (0=sun through to 6 =sat) =TODAY()-B9-WEEKDAY(TODAY()-B9-*2*)+1 so if you always want to find a Sunday it's just =TODAY()-B9-WEEKDAY(TODAY()-B9)+1 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=537744 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting based on a date + 30 days | Excel Discussion (Misc queries) | |||
| set a date in columns 7 days apart | Excel Worksheet Functions | |||
| Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
| Roll back to previous date | Excel Worksheet Functions | |||
Linear Mode
