Date Formula Help!!

[gareth.miles]

I have a daily report that I am in the process of automating. The
report needs to date sales from the day before, excluding the weekend.

E.g. today is Friday 28th, the report is run for Thurs 27th. I can do
this using the =TODAY()-1 function but this comes unstuck on Mondays,
as the report then lists Sundays date and I need it to list Fridays
instead.

Does anyone know of a way to do this??

Cheers!

 

[David Biddulph]

You could use WEEKDAY as an input to an IF function, or use the WORKDAY
function. You'll find the relevant functions in Excel help.

 

[Max]

Try:
=IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1)

 

[gareth.miles]

Thanks for the quick response, that formula is bringing up yesterdays
date but when I change my PC clock to a Monday it still brings up
yesterdays date (as in the 27th not Sunday 30th).

Can you explain the formula step by step so I can understand what it's
trying to achieve?

Thanks again for your help, it's much appreciated!

 

[David Biddulph]

The functions which Max used are standard Excel functions. IF, WEEKDAY, and
TODAY are all listed in Excel help, so that you can look at it step by step.

Are you sure that Tools/ Options/ Calculation is set to Automatic, not
Manual?
Have you updated your spreadsheet since you changed your PC clock? Did you
try saving it?
What does the function =TODAY() show you, when your PC clock is set to
Monday's date and Max's formula supposedly gives yesterday's Thursday date?

 

[Ron Rosenfeld]

I have a daily report that I am in the process of automating. The
report needs to date sales from the day before, excluding the weekend.

E.g. today is Friday 28th, the report is run for Thurs 27th. I can do
this using the =TODAY()-1 function but this comes unstuck on Mondays,
as the report then lists Sundays date and I need it to list Fridays
instead.

Does anyone know of a way to do this??

Cheers!

You can use the WORKDAY function:

=WORKDAY(TODAY(),-1)

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
--ron

 

[gareth.miles]

Cheers David,

It was set to auto update but for some reason it doesn't. When I
press F9 it brings up the correct date.

Thanks for all your help gents!