SOLVED

please help

%3CLINGO-SUB%20id%3D%22lingo-sub-1880857%22%20slang%3D%22en-US%22%3Eplease%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1880857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22125057667_400937801040610_3169441830548493151_n.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233171iC9D07DD5B23B734C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22125057667_400937801040610_3169441830548493151_n.png%22%20alt%3D%22125057667_400937801040610_3169441830548493151_n.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20the%20date%20will%20not%20take%20place%20during%20vacations%2C%20public%20holidays%20and%20weekends.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1880857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881052%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881052%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866856%22%20target%3D%22_blank%22%3E%40safoine%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20interesting.%20I%20have%20entered%20the%20same%20formula%20and%20am%20not%20receiving%20an%20error.%20Perhaps%20you%20could%20open%20a%20new%20worksheet%20and%20enter%20the%20formula%20again%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PReagan_0-1605194252489.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233179i134B006D3F4A50C8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PReagan_0-1605194252489.png%22%20alt%3D%22PReagan_0-1605194252489.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENote%20that%20this%20data%20validation%20allows%20only%20dates%20that%20are%20not%20Saturday's%20and%20Sunday's.%20It%20does%20not%20account%20for%20vacations%20and%20holidays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881139%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22safoine_0-1605195682763.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233184iC3C1C7DC40BC7354%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22safoine_0-1605195682763.png%22%20alt%3D%22safoine_0-1605195682763.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20again%20and%20it%20doesn't%20work.%20yes%20I%20know%20its%20for%20Saturday%20and%20Sunday%20that's%20why%20I%20tried%20an%20easy%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881140%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866856%22%20target%3D%22_blank%22%3E%40safoine%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esaturday%2Fsunday%3C%2FP%3E%3CP%3E%3DC3%3D(WORKDAY(C3-1%2C1))%3C%2FP%3E%3CP%3E%3DC3%3D(WORKDAY.INTL(C3-1%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20holidays%20and%26nbsp%3B%3CSPAN%3Evacations%20in%20a%20list%20on%20the%20sheet%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMyHolidaySheet!%24A%241%3A%24A%2417%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DC3%3D(WORKDAY(C3-1%2C1%2CMyHolidaySheet!%24A%241%3A%24A%2417))%3C%2FP%3E%3CP%3E%3DC3%3D(WORKDAY.INTL(C3-1%2C1%2C%2CMyHolidaySheet!%24A%241%3A%24A%2417))%3C%2FP%3E%3CP%3EI'm%20not%20sure%20about%20the%20difference%20between%26nbsp%3BWORKDAY%20and%20WORKDAY.INTL.%3C%2FP%3E%3CP%3EWORKDAY%20might%20pick%20up%20%22ordinary%22%20bank-holidays%20in%20a%20US%20system.%20It%20does%20not%20with%20my%20regional%20settings.%26nbsp%3B%3C%2FP%3E%3CP%3ETry%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881148%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857441%22%20target%3D%22_blank%22%3E%40MindreVetande%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22safoine_0-1605196127150.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233185i608E16C2246EFA1B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22safoine_0-1605196127150.png%22%20alt%3D%22safoine_0-1605196127150.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Esame%26nbsp%3Bproblem%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881166%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881166%22%20slang%3D%22en-US%22%3E1.%20Are%20you%20absolutely%20sure%20your%20computers%20language%20setting%20is%20US%2FEnglish%3F%20In%20some%20other%20languages%20we%20use%20%3B%20instead%20off%2C%20as%20separator%3CBR%20%2F%3E2.%20In%20some%20cases%20WEEKday%20starts%20from%200%20instead%20of%201.%20Try%20the%20Weekday(C3)%20outside%20of%20%22data%20validation%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881220%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881220%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866856%22%20target%3D%22_blank%22%3E%40safoine%3C%2FA%3E%26nbsp%3BPerhaps%20your%20local%20settings%20require%20a%20semi-colon%20to%20separate%20arguments%20in%20formulae.%3C%2FP%3E%3CP%3ETry%26nbsp%3B%3CSTRONG%3E%3DAND(WEEKDAY(A2)%26lt%3B%26gt%3B1%3CFONT%20color%3D%22%23FF0000%22%3E%3B%3C%2FFONT%3EWEEKDAY(A2)%26lt%3B%26gt%3B7)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOooops%3A%20didn't%20see%20the%20previous%20post%20until%20I%20posted%20mine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881430%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881430%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857441%22%20target%3D%22_blank%22%3E%40MindreVetande%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20a%20lot%2C%20it's%20working%26nbsp%3B%3C%2FP%3E%3CP%3Edo%20you%20have%20any%20formula%20to%20make%3CSPAN%3E%26nbsp%3Bthe%20date%20will%20not%20take%20place%20during%20vacations%2C%20public%20holidays%20and%20weekends%20please%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ei%20create%20two%20lists%20public%20holidays%20and%20vacations%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881434%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881434%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20a%20lot%20it's%20working%26nbsp%3B%3C%2FP%3E%3CP%3Edo%20you%20have%20any%20formula%20to%20make%3CSPAN%3E%26nbsp%3Bthe%20date%20will%20not%20take%20place%20during%20vacations%2C%20public%20holidays%20and%20weekends%20please%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ei%20create%20two%20liste%20public%20holidays%20and%20vacations.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881882%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866856%22%20target%3D%22_blank%22%3E%40safoine%3C%2FA%3E%26nbsp%3BPerhaps%20best%20demonstrated%20in%20the%20attached%20file.%20See%20if%20you%20can%20%22translate%22%20the%20data%20validation%20to%20your%20own%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1882175%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1882175%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20appreciate%20your%3CSPAN%3E%26nbsp%3Bhelp%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eso%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20make%20the%20error%20message%20depend%20on%20data%20is%20entered%3FFor%20example%20if%20the%20day%20is%20weekend%20the%20error%20message%20tell%20me%20it's%20Sunday%20or%20Saturday%20or%20if%20its%20holiday%20the%20error%20message%20tells%20that's%20a%20holiday.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sans%20titre.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233238i8F836D9A5CD1CB4B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sans%20titre.png%22%20alt%3D%22Sans%20titre.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

125057667_400937801040610_3169441830548493151_n.png

I want the date will not take place during vacations, public holidays and weekends.

12 Replies

Hello @safoine,

 

That's interesting. I have entered the same formula and am not receiving an error. Perhaps you could open a new worksheet and enter the formula again?

PReagan_0-1605194252489.png

Note that this data validation allows only dates that are not Saturday's and Sunday's. It does not account for vacations and holidays.

 

@PReagan 

safoine_0-1605195682763.png 

I tried again and it doesn't work. yes I know its for Saturday and Sunday that's why I tried an easy formula.

 

@safoine 

saturday/sunday

=C3=(WORKDAY(C3-1,1))

=C3=(WORKDAY.INTL(C3-1,1))

 

With holidays and vacations in a list on the sheet:

MyHolidaySheet!$A$1:$A$17

=C3=(WORKDAY(C3-1,1,MyHolidaySheet!$A$1:$A$17))

=C3=(WORKDAY.INTL(C3-1,1,,MyHolidaySheet!$A$1:$A$17))

I'm not sure about the difference between WORKDAY and WORKDAY.INTL.

WORKDAY might pick up "ordinary" bank-holidays in a US system. It does not with my regional settings. 

Try

 

@MindreVetande 

safoine_0-1605196127150.png

same problem

best response confirmed by safoine (Occasional Contributor)
Solution
1. Are you absolutely sure your computers language setting is US/English? In some other languages we use ; instead off, as separator
2. In some cases WEEKday starts from 0 instead of 1. Try the Weekday(C3) outside of "data validation"

@safoine Perhaps your local settings require a semi-colon to separate arguments in formulae.

Try =AND(WEEKDAY(A2)<>1;WEEKDAY(A2)<>7)

 

Oooops: didn't see the previous post until I posted mine.

 

Hello @MindreVetande 

thank you a lot, it's working 

do you have any formula to make the date will not take place during vacations, public holidays and weekends please ?

i create two lists public holidays and vacations 

Hello @Riny_van_Eekelen 

thank you a lot it's working 

do you have any formula to make the date will not take place during vacations, public holidays and weekends please ?

i create two liste public holidays and vacations.

@safoine Perhaps best demonstrated in the attached file. See if you can "translate" the data validation to your own workbook.

 

hello @Riny_van_Eekelen

I appreciate your help so much

 

Can I make the error message depend on data is entered?For example if the day is weekend the error message tell me it's Sunday or Saturday or if its holiday the error message tells that's a holiday.

 
 

Sans titre.png

@safoine In the Input message you could stat that the date should not be a weekend or a holiday. Then in the error message you can write a more friendly message explaining what input is accepted, but I don't think it's possible to create an error message depending on the particular reason for the error (i.e. Saturday, Sunday or Holiday). But perhaps I'm wrong. Don't know.

@Riny_van_Eekelen

thank you I value the help you've given me.