MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Friday


Posted by stevie windows on August 31, 2001 3:17 AM

Anyone help. I need to insert a date into a worksheet. I was using the Now() function, but the user now wants the date to show the closest Friday following the date that Now() would return. ie. today is Friday, so it would return Friday 31/8/01. It would also return that date all of last week up to last Saturday, and from tomorrow until next Friday it will return next Friday's date. I'm sure this is a simple formula, but I don't know where to start, anyone help?


Posted by Ian on August 31, 2001 4:11 AM

in the cell next to the now() try

=a1+VLOOKUP(TEXT(a1,"dddd"),{"Saturday",6;"Sunday",5;"Monday",4;"Tuesday",3;"Wednesday",2;"Thursday",1;"Friday",0},2,0)

b1 being the cell I used for the Now() thing

any good

Ian

Posted by Dax on August 31, 2001 4:24 AM

or you could try:-

=IF(WEEKDAY(NOW())=6,NOW(),IF(WEEKDAY(NOW())=7,NOW()+6-WEEKDAY(NOW())))


Regards,
Dax.

Posted by Aladin Akyurek on August 31, 2001 4:43 AM

I believe the following should do the job:

=IF(WEEKDAY(CurDay)=6,CurDay,CurDay+IF(6<WEEKDAY(CurDay),7-WEEKDAY(CurDay)+6,6-WEEKDAY(CurDay)))

CurDay is a named dynamic formula.

Activate the option Insert|Name|Define.
Enter "Curday" (without double quotes) as name in the Names in Workbook box.
Enter the following formula in the Refers To box:

=TODAY()

Click OK.

Aladin

Posted by Aladin Akyurek on August 31, 2001 4:46 AM

I'm hoping this is an easy one. I want to be able to have a macro which will bring up the Save as command window. Basically, is it possible to run the saveas command from VBA with out specifying the name of the file in the code?

Posted by stevie windows on August 31, 2001 5:21 AM

Cheers, this seems to work. (I'll know for sure on Monday!)

Posted by IML on August 31, 2001 7:28 AM

stealing from Dax

I tried the same route as Dax, and came up with little different result if 8/25 is the date used (I think maybe just the last argument in his if statement didn't get pasted)

For what it's worth,

=IF(WEEKDAY(TODAY())=6,TODAY(),IF(WEEKDAY(TODAY())=7,TODAY()+6,TODAY()+6-WEEKDAY(TODAY())))

With today() or now()being interchangeable.

good luck.

Posted by lenze on August 31, 2001 7:35 AM

Good suggestions below. I think this will work also.

=CHOOSE(WEEKDAY(NOW()),NOW()+5,NOW()+4,NOW()+3,NOW()+2,NOW()+1,NOW(),NOW()+6)

Anyone help. I need to insert a date into a worksheet. I was using the Now() function, but the user now wants the date to show the closest Friday following the date that Now() would return. ie. today is Friday, so it would return Friday 31/8/01. It would also return that date all of last week up to last Saturday, and from tomorrow until next Friday it will return next Friday's date. I'm sure this is a simple formula, but I don't know where to start, anyone help?

Posted by Robb on August 31, 2001 9:11 PM

Stevie

You could try this, if you need:

=(IF(WEEKDAY(NOW())=6,NOW(),NOW()+((7-WEEKDAY(NOW()))+6)))

Regards

Robb Anyone help. I need to insert a date into a worksheet. I was using the Now() function, but the user now wants the date to show the closest Friday following the date that Now() would return. ie. today is Friday, so it would return Friday 31/8/01. It would also return that date all of last week up to last Saturday, and from tomorrow until next Friday it will return next Friday's date. I'm sure this is a simple formula, but I don't know where to start, anyone help?

Posted by Aladin Akyurek on September 01, 2001 12:47 AM

My initial reply was victimized by the script that runs this site because of the "less than" sign that it contains. My attempt to correct that appears to have gone completely wrong thanks to the same script. That's why I reproduce here my initial reply. I believe the following should do the job: =IF(WEEKDAY(CurDay)=6,CurDay,CurDay+IF(6 < WEEKDAY(CurDay),7-WEEKDAY(CurDay)+6,6-WEEKDAY(CurDay))) CurDay is a named dynamic formula. Activate the option Insert|Name|Define.

Posted by Robb on September 01, 2001 6:47 PM

Should have typed:
=(IF(WEEKDAY(NOW())=6,NOW(),IF(WEEKDAY(NOW())=7,NOW()+6,NOW()+((6-WEEKDAY(NOW()))))))

Stevie You could try this, if you need: =(IF(WEEKDAY(NOW())=6,NOW(),NOW()+((7-WEEKDAY(NOW()))+6))) Regards Robb : Anyone help. I need to insert a date into a worksheet. I was using the Now() function, but the user now wants the date to show the closest Friday following the date that Now() would return. ie. today is Friday, so it would return Friday 31/8/01. It would also return that date all of last week up to last Saturday, and from tomorrow until next Friday it will return next Friday's date. I'm sure this is a simple formula, but I don't know where to start, anyone help?