Formula Now()

brettvba · May 16th, 2002, 09:48 PM

What can I change so that the date in this formula is always a day of the week
=now()+30

if it equals saturday or sunday I want it to round the date up or down to the nearest weekday

Any Ideas

PaddyD · May 16th, 2002, 10:01 PM

You could try using the weekday function to determine whether now()+30 returns a 1 (sunday) or a 7 (saturday) & change accordingly.

This should work,

=IF(WEEKDAY(NOW()+30)=1,NOW()+31,IF(WEEKDAY(NOW()+30)=7,NOW()+29,NOW()+30))

Paddy

brettvba · May 16th, 2002, 10:06 PM

Thanks Paddy just what I needed

Aladin Akyurek · May 16th, 2002, 10:59 PM

=WEEKDAY(NOW(),30) or

=WEEKDAY(TODAY(),30)

will suffice.

You can even exclude the holidays. See the Help file how that's done.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 09:48 PM	#1
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	What can I change so that the date in this formula is always a day of the week =now()+30 if it equals saturday or sunday I want it to round the date up or down to the nearest weekday Any Ideas

May 16th, 2002, 10:01 PM	#2
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	You could try using the weekday function to determine whether now()+30 returns a 1 (sunday) or a 7 (saturday) & change accordingly. This should work, =IF(WEEKDAY(NOW()+30)=1,NOW()+31,IF(WEEKDAY(NOW()+30)=7,NOW()+29,NOW()+30)) Paddy

May 16th, 2002, 10:06 PM	#3
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	Thanks Paddy just what I needed

May 16th, 2002, 10:59 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	=WEEKDAY(NOW(),30) or =WEEKDAY(TODAY(),30) will suffice. You can even exclude the holidays. See the Help file how that's done.