updating date!

[via135]

hi all!

i am updating a worksheet on daily basis and one of the cell is having the formula =(TODAY()-1), since i am updating the figures of the earlier day!
what i want is when i am opening the file on Monday as well as on any other day immediately following a holiday (will be defined by me) the cell should automatically skip the "Sunday" and the other "Holidays"!!

hlp pl!!

-via135

[Pete_UK]

If you use another cell to enable you to record the number of extra
days to omit, eg in A1, then your formula could be amended as follows:

=(TODAY()-1-A1)

If A1 is blank or zero it will act like your current formula. If it is
Monday after a normal weekend, enter 2 into A1. If it is the first day
back after a 4-day holiday, enter 4 in A1. And so on.

Hope this helps.

Pete

[Fred Smith]

I can help you out on the Mondays.

=today()-if(mod(today()-2,7)=0,3,1)

will return Friday if today is a Monday, else the previous day.

For holidays, you'll probably have to build a table and use Lookup or Match.



--
Regards,
Fred


"via135" <[email protected]> wrote in message
news:[email protected]...
>
> hi all!
>
> i am updating a worksheet on daily basis and one of the cell is having
> the formula =(TODAY()-1), since i am updating the figures of the
> earlier day!
> what i want is when i am opening the file on Monday as well as on any
> other day immediately following a holiday (will be defined by me) the
> cell should automatically skip the "Sunday" and the other "Holidays"!!
>
> hlp pl!!
>
> -via135
>
>
> --
> via135
> ------------------------------------------------------------------------
> via135's Profile:
> http://www.excelforum.com/member.php...o&userid=26725
> View this thread: http://www.excelforum.com/showthread...hreadid=514997
>

[via135]

thks both of you Pete & Smith!

but.. using a holiday range would even better??!!

-via135

I can help you out on the Mondays.

=today()-if(mod(today()-2,7)=0,3,1)

will return Friday if today is a Monday, else the previous day.

For holidays, you'll probably have to build a table and use Lookup or Match.



--
Regards,
Fred


"via135" <[email protected]> wrote in message
news:[email protected]...
>
> hi all!
>
> i am updating a worksheet on daily basis and one of the cell is having
> the formula =(TODAY()-1), since i am updating the figures of the
> earlier day!
> what i want is when i am opening the file on Monday as well as on any
> other day immediately following a holiday (will be defined by me) the
> cell should automatically skip the "Sunday" and the other "Holidays"!!
>
> hlp pl!!
>
> -via135
>
>
> --
> via135
> ------------------------------------------------------------------------
> via135's Profile:
> http://www.excelforum.com/member.php...o&userid=26725
> View this thread: http://www.excelforum.com/showthread...hreadid=514997
>