Dates in Excel 3

[lyndseykinder]

I am currently creating a spreadsheet containing daily targets which will give me a running total. I've manged to get the formulae to work using a single date field which is ok, however, I really need to automatically pick a historic date.

For example when going into the sheet on Monday, the date needs to be friday, tuesday needs to be monday...etc and preferably I need to factor in bank holidays.

So far I have done
=if(today()=dd/mm/yyyy,today()-3,today()-1)

This seems to ignore today()-3 and give me today()-1!

I'm sure I'm doing something glaringly obvious, but, I'd really appreciate some help!! )
Thanks
Lyndsey

 

[AnotherHiggins]

The syntax for an if statement is as follows:

=if(condition,ResultIfTrue,ResultIfFalse)

Your condition is that today = dd/mm/yyyy. Not that today is monday, but just that today = dd/mm/yyyy. That doesn't mean anything.

Try this instead:[tt]
=If(WeekDay(Today())=2,Today()-3,Today())[green]
^---Condition----^,^If True^,^IfFalse^[/green]
[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

Make sure the Analysis Toolpak is added-in ( menu command Tools/Add-ins and tick the relevant choices ), and use the Workday function.

=WORKDAY(TODAY(),-1)

If you have a list of holidays you can these taken into account too.

=WORKDAY(TODAY(),-1,MyHolsList)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

or...
[tt]
=if(text(today(),"mmm")="Mon",today()-3,today()-1)
[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

Er, I think Skip meant:

=if(text(today(),"[!]DDD[/!]")="Mon",today()-3,today()-1)

Or have they gone and invented a new month?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lyndseykinder]

Thanks John,

That's great for identifying Friday when it's Monday, but, is it possible to hard code a date in there to deal with bank holidays.

So if yesterday is 01/01/2008 then subtract 4 days to go to Friday?

Thanks
Lyndsey

 

[AnotherHiggins]

See Glenn's post involving WorkDay.

WorkDay already ignores weekends. So all you need is a way to deal with holidays. WorkDay can do that, too.

On another sheet - a hidden sheet if you don't want to look at it - you'll need to list all bank holidays.

Then, as Glenn offered, you just use:
=WorkDay(Today(),-1,Holidays!$A$1:$A$50)

Or if you use a Named Range (perhaps BankHolidays) for the list of holidays,
=WorkDay(Today(),-1,BankHolidays)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lyndseykinder]

Thank you all for your help
:OD

 

[SkipVought]

oops! Thanx for the save, John!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

It takes a village". Or something like that....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.