Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE WORKDAY PROBLEM
hi, not good with dates, trying to have readout show: "next workday is a
holiday.. " - to include today is sat/sun (having a problem with) artificial set monday as a holiday defined name HOL: ={"2010-01-01";"2010-01-11";"2010-01-18"} fe3 = NOW() or: 2010.01.08 4:27:29 PM (changing pc date to yesterday/today/tomarrow: sat) have some items that work below /not labelled: yes / no. last entry at top.. thanks in advance. example: works: y/n =TODAY()=WORKDAY($FE$3,0,HOL) works, but not if today is sat/sun =TODAY()+1=WORKDAY($FE$3,1,HOL) wrong.. =WORKDAY($FE$3,1,HOL)-1 no/ trying to do fe3 +1 ?? =DATE(2010,1,8) basic/yes =TODAY()=DATE(2010,1,9)-1 yes =TODAY()=DATE(2010,1,8) yes =SUMPRODUCT(--ISNUMBER(FIND(HOL,TODAY())))0 no =WORKDAY($FE$3,1,HOL) yes fe3 has NOW(): 2010.01.08 4:27:29 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE WORKDAY PROBLEM
I may have this wrong, but... as I understand it, you're only concerned with
one date at a time and whether or not the day following it is a holiday (with Saturdays and Sundays being considered holidays). If that is correct, then this formula should help some: =IF(NETWORKDAYS($FE$3,$FE$3+1,HOL)=0,"Both are 'holiday's",IF(NETWORKDAYS($FE$3,$FE$3+1,HOL)=2,"N either is a holiday",IF(NETWORKDAYS($FE$3+1,$FE$3+1,HOL)=0,"To morrow is a holiday","Today is a holiday (tomorrow is not)"))) "XLtest" wrote: hi, not good with dates, trying to have readout show: "next workday is a holiday.. " - to include today is sat/sun (having a problem with) artificial set monday as a holiday defined name HOL: ={"2010-01-01";"2010-01-11";"2010-01-18"} fe3 = NOW() or: 2010.01.08 4:27:29 PM (changing pc date to yesterday/today/tomarrow: sat) have some items that work below /not labelled: yes / no. last entry at top.. thanks in advance. example: works: y/n =TODAY()=WORKDAY($FE$3,0,HOL) works, but not if today is sat/sun =TODAY()+1=WORKDAY($FE$3,1,HOL) wrong.. =WORKDAY($FE$3,1,HOL)-1 no/ trying to do fe3 +1 ?? =DATE(2010,1,8) basic/yes =TODAY()=DATE(2010,1,9)-1 yes =TODAY()=DATE(2010,1,8) yes =SUMPRODUCT(--ISNUMBER(FIND(HOL,TODAY())))0 no =WORKDAY($FE$3,1,HOL) yes fe3 has NOW(): 2010.01.08 4:27:29 PM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE WORKDAY PROBLEM
Added, thought, again based on the understanding I explained:
If you are ONLY concerned with what type of day 'tomorrow' (the day after the date in FE3) is, then this will handle that one little situation: =IF(NETWORKDAYS($FE$3+1,$FE$3+1,HOL)=0,"Tomorrow is a holiday","tomorrow is not a holiday") "XLtest" wrote: hi, not good with dates, trying to have readout show: "next workday is a holiday.. " - to include today is sat/sun (having a problem with) artificial set monday as a holiday defined name HOL: ={"2010-01-01";"2010-01-11";"2010-01-18"} fe3 = NOW() or: 2010.01.08 4:27:29 PM (changing pc date to yesterday/today/tomarrow: sat) have some items that work below /not labelled: yes / no. last entry at top.. thanks in advance. example: works: y/n =TODAY()=WORKDAY($FE$3,0,HOL) works, but not if today is sat/sun =TODAY()+1=WORKDAY($FE$3,1,HOL) wrong.. =WORKDAY($FE$3,1,HOL)-1 no/ trying to do fe3 +1 ?? =DATE(2010,1,8) basic/yes =TODAY()=DATE(2010,1,9)-1 yes =TODAY()=DATE(2010,1,8) yes =SUMPRODUCT(--ISNUMBER(FIND(HOL,TODAY())))0 no =WORKDAY($FE$3,1,HOL) yes fe3 has NOW(): 2010.01.08 4:27:29 PM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE WORKDAY PROBLEM
I think this will work for you:
=IF(ISNUMBER(MATCH(WORKDAY(TODAY(),1),$G$1:$G$2,0) ),"Next workday is holiday","Next workday is not a holiday") Formula finds next workday after today, and if it is in list of holidays, outputs correct text string. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "XLtest" wrote: hi, not good with dates, trying to have readout show: "next workday is a holiday.. " - to include today is sat/sun (having a problem with) artificial set monday as a holiday defined name HOL: ={"2010-01-01";"2010-01-11";"2010-01-18"} fe3 = NOW() or: 2010.01.08 4:27:29 PM (changing pc date to yesterday/today/tomarrow: sat) have some items that work below /not labelled: yes / no. last entry at top.. thanks in advance. example: works: y/n =TODAY()=WORKDAY($FE$3,0,HOL) works, but not if today is sat/sun =TODAY()+1=WORKDAY($FE$3,1,HOL) wrong.. =WORKDAY($FE$3,1,HOL)-1 no/ trying to do fe3 +1 ?? =DATE(2010,1,8) basic/yes =TODAY()=DATE(2010,1,9)-1 yes =TODAY()=DATE(2010,1,8) yes =SUMPRODUCT(--ISNUMBER(FIND(HOL,TODAY())))0 no =WORKDAY($FE$3,1,HOL) yes fe3 has NOW(): 2010.01.08 4:27:29 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to shift a weekend date to the last or next workday | Excel Discussion (Misc queries) | |||
Workday Problem | Excel Discussion (Misc queries) | |||
Workday return a Saturday date | Setting up and Configuration of Excel | |||
Workday Date Calculation | Excel Worksheet Functions | |||
workday date function | Excel Worksheet Functions |