Week ending date as a formula

Posted by Wilfred on June 15, 2000 6:00 AM

Can any one help.

I need a formula to calculate the week ending date (which is on a Sunday).
So whatever day you want to open the workbook the date will automatically change to this Sundays date.

Can any one help me please?

I was helped out in March with this (for previous Saturday date):

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

I can't seem to change it to this Sundays date (18/06/00)....Doh!.

Cheers,

Wilfred.

Posted by JAF on June 16, 0100 1:09 AM

There may be a way to do this without all these IF statements, but the following does work . . .

'=IF(WEEKDAY(TODAY())=2,TODAY()+6,IF(WEEKDAY(TODAY())=3,TODAY()+5,IF(WEEKDAY(TODAY())=4,TODAY()+4,IF(WEEKDAY(TODAY())=5,TODAY()+3,IF(WEEKDAY(TODAY())=6,TODAY()+2,IF(WEEKDAY(TODAY())=7,TODAY()+1,TODAY()))))))

JAF

Posted by Norman on June 16, 0100 1:57 AM

Great stuff

Great it works.

Thanks.

Posted by mads on June 16, 0100 2:24 AM

Here's a shorter one :-

=IF(WEEKDAY(TODAY())=1,TODAY(),TODAY()-WEEKDAY(TODAY())+8)

mads