Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
updating date!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
updating date!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
updating date!
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
updating date!
thks both of you Pete & Smith! but.. using a holiday range would even better??!! -via135 Fred Smith Wrote: 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" wrote in message ... 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 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514997 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop a date from automatically updating? | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
date and time | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |