MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro/Formula - Week commencing calculation


Posted by Wilfred on April 13, 2000 7:52 AM

Can any one help.

I need a Macro, better still a formula to calculate (when you open workbook) the week begining date (which starts on a Saturday).
So whatever day you want to go into the workbook the date will automatically change to the previous Saturdays date.

The date is to be inserted into a cell eg. "I2"

I'm I being clear? And can any one help me please?

Cheers,

Wilfred.


Posted by Joe on April 13, 2000 12:35 PM

Posted by Joe on April 13, 2000 12:35 PM

Posted by Joe on April 13, 2000 12:39 PM

Re: Week commencing calculation

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

Sorry about the multiple bland postings.

Joe


Posted by Mark on April 14, 2000 10:22 AM

Wilfred

An alternate to the IF statement is:

=TODAY()-(TODAY()-INT(TODAY()/7)*7)

with some date format in the cell.

Mark