Help with Workday formula

%3CLINGO-SUB%20id%3D%22lingo-sub-182445%22%20slang%3D%22en-US%22%3EHelp%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182445%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20issue%20with%20the%20workday%20formula%20calculating%201%20Jan%202018%20(which%20is%20also%20bank%20holiday)%20to%20show%20next%20working%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EA1%3A%26nbsp%3B01%2F01%2F2018%3C%2FP%3E%3CP%3EA2%3A%26nbsp%3B01%2F01%2F2018%20(holiday)%3C%2FP%3E%3CP%3EA3%3A%26nbsp%3B%3DWORKDAY(A1%2C1%2CA2)%26nbsp%3B%20%26nbsp%3B--%26gt%3B%20Result%20%3D%2002%2F01%2F2018%20(2nd%20January%20instead%20of%203rd%20January%2C%20which%20is%20the%20next%20working%20day%20as%201st%20Jan%20is%20holiday).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-182445%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Formula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1648491%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20made%20the%20attached%20excel%20project%20plan%20and%20it%20is%20working%20fine%20but%20I%20have%20to%20select%20holidays%20manually%20by%20seeing%20the%20country%20of%20the%20assignee.%20I%20want%20this%20to%20be%20done%20as%20per%20the%20countries%20defined%20in%20the%20project%20plan%20by%20seeing%20the%20list%20of%20per%20country%20holidays%20from%20the%20field%20value%20of%20the%20assignee%20location.%20My%20excel%20sheet%20is%20attached%20to%20this%20email.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20using%20this%20formula%20to%20calculate%20the%20start%20and%20end%20dates%20of%20each%20task%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3DWORKDAY(E12%2C(F12)%2CHolidays!%24B%245%3A%24B%248)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20tried%20to%20change%20the%20above%20formula%20by%20adding%20different%20conditions%20but%20nothing%20works.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20also%20looking%20to%20protect%2Flock%20formula%20cells%20dynamically%2C%20i.e.%2C%20when%20we%20defined%20formula.%20Is%20it%20possible%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20also%20looking%20to%20have%20different%20colors%20for%20formula%20fields%20dynamically.%20I%20don't%20know%20whether%20it%20is%20possible%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20lost%20a%20lot%20of%20my%20time%20to%20sort%20out%20this%20formula%20but%20none%20was%20successful.%20Your%20kind%20help%20is%20appreciated.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%20in%20advance%20for%20your%20help.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMuzaffar%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182478%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182478%22%20slang%3D%22en-US%22%3EThank%20you%2C%20much%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EGrace%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182477%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182477%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20using%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%3E%3DWORKDAY(IF(A1%3DA2%2CA1%2B1%2CA1)%2C1%2CA2)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20portion%20of%20the%20formula%26nbsp%3B%3CSTRONG%3EIF(A1%3DA2%2CA1%2B1%2CA1)%20%3C%2FSTRONG%3Ewill%20check%20if%20the%20start%20date%20is%20equal%20to%20the%20holiday.%3C%2FP%3E%3CP%3EIf%20so%2C%20it%20will%20add%20one%20day%20to%20the%20start%20day%2C%20otherwise%2C%20will%20still%20as%20it%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182468%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182468%22%20slang%3D%22en-US%22%3EHi%20Haytham%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20explanation.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20recommend%20the%20formula%20so%20that%20for%201st%20Jan%20is%20accounted%20for%20holiday%20as%20it%20is%20the%20start%20date%3F%20So%20Excel%20can%20find%20that%202%20Jan%20is%20the%20actual%20start%20date%20(acknowledging%20the%20start%20date%20is%20holiday)%20and%20the%20so%20than%20it%20can%20then%20find%20that%20the%20next%20working%20day%20so%20the%20result%20showing%20as%203%20Jan%3F%3CBR%20%2F%3E%3CBR%20%2F%3EGrace%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182462%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182462%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20of%20the%20formula%20is%20correct!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fworkday-function-f764a5b7-05fc-4494-9486-60d494efbf33%3FNS%3DEXCEL%26amp%3BVersion%3D16%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL160%26amp%3BHelpId%3Dxlmain11.chm60519%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EWORKDAY%3C%2FA%3E%20function%20works%20this%20way%3A%3C%2FP%3E%3CUL%3E%3CLI%3EIt%20looks%20at%20the%20start%20date.%3C%2FLI%3E%3CLI%3EIt%20skips%20the%20to%20the%20next%20workday%20that%20is%20%3CSPAN%3Enonweekend%20and%20nonholiday.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EThe%20number%20of%20skips%20is%20based%20on%20the%20second%20argument%20of%20the%20function%20(days).%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EYou%20have%20an%20option%20to%20define%20the%20holidays%20in%20the%20third%20argument%2C%20t%3CSPAN%20class%3D%22short_text%22%3Eo%20make%20the%20function%20take%20them%20into%20account.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E%3CSPAN%20class%3D%22short_text%22%3EThe%20holidays'%20argument%20is%20optional%2C%20you%20can%20omit%20it%2C%20which%20is%20means%20that%20there%20are%20no%26nbsp%3Bholidays.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E%3CSPAN%20class%3D%22short_text%22%3ENo%20matter%20if%20the%20start%20date%20is%20a%20holiday%20or%26nbsp%3Bweekend%2C%20it's%20just%20a%20starting%20point.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20the%20formula%20above%2C%20since%20the%20start%20date%20is%26nbsp%3B01%2F01%2F2018%20and%20the%20number%20of%20days%20(skips)%20is%201%2C%20it%20will%20return%20the%20next%20workday%2002%2F01%2F2018%20which%20is%20nonweekend%20and%20nonholiday.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20formula%20will%20return%2003%2F01%2F2018%20in%20case%20if%20you%20make%20the%20day%20of%2002%2F01%2F2018%20is%20the%20holiday.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3EHope%20that%20helps%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EHaytham%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182461%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182461%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20response.%20But%20in%20the%20formula%20stated%20that%201st%20Jan%20is%20a%20holiday%20hence%20I%20expect%20that%20the%20first%20working%20day%20is%20on%20the%202nd%2C%20and%20the%20next%20working%20day%20is%20the%203rd%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGrace%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182451%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Workday%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182451%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Grace%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20correct%20result%20-%20after%20Mon%2C%2001%20Jan%20the%20next%20working%20day%20is%20Tue%2C%2002%20Jan%20since%20it's%20not%20day-off%20(not%20public%20holiday%20and%20not%20weekend).%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 

I am having issue with the workday formula calculating 1 Jan 2018 (which is also bank holiday) to show next working day.

 

For example:

A1: 01/01/2018

A2: 01/01/2018 (holiday)

A3: =WORKDAY(A1,1,A2)   --> Result = 02/01/2018 (2nd January instead of 3rd January, which is the next working day as 1st Jan is holiday).

 

 

7 Replies

Hi Grace,

 

That's correct result - after Mon, 01 Jan the next working day is Tue, 02 Jan since it's not day-off (not public holiday and not weekend).

Hi Sergei,

 

Thanks for the quick response. But in the formula stated that 1st Jan is a holiday hence I expect that the first working day is on the 2nd, and the next working day is the 3rd?

 

Grace

Hi,

 

The result of the formula is correct!

 

Please note that the WORKDAY function works this way:

  • It looks at the start date.
  • It skips the to the next workday that is nonweekend and nonholiday.
  • The number of skips is based on the second argument of the function (days).
  • You have an option to define the holidays in the third argument, to make the function take them into account.
  • The holidays' argument is optional, you can omit it, which is means that there are no holidays.
  • No matter if the start date is a holiday or weekend, it's just a starting point.

 

In the formula above, since the start date is 01/01/2018 and the number of days (skips) is 1, it will return the next workday 02/01/2018 which is nonweekend and nonholiday.

 

The formula will return 03/01/2018 in case if you make the day of 02/01/2018 is the holiday.

 

Hope that helps

Haytham

Hi Haytham,

Thanks for the explanation.

Can you recommend the formula so that for 1st Jan is accounted for holiday as it is the start date? So Excel can find that 2 Jan is the actual start date (acknowledging the start date is holiday) and the so than it can then find that the next working day so the result showing as 3 Jan?

Grace

Hi,

 

I suggest using this formula instead:

=WORKDAY(IF(A1=A2,A1+1,A1),1,A2)

 

This portion of the formula IF(A1=A2,A1+1,A1) will check if the start date is equal to the holiday.

If so, it will add one day to the start day, otherwise, will still as it is.

Thank you, much appreciated.

Grace

@Haytham Amairah 

Hi,

I have made the attached excel project plan and it is working fine but I have to select holidays manually by seeing the country of the assignee. I want this to be done as per the countries defined in the project plan by seeing the list of per country holidays from the field value of the assignee location. My excel sheet is attached to this email.

I am using this formula to calculate the start and end dates of each task:

=WORKDAY(E12,(F12),Holidays!$B$5:$B$8)

I have tried to change the above formula by adding different conditions but nothing works.

I am also looking to protect/lock formula cells dynamically, i.e., when we defined formula. Is it possible?

I am also looking to have different colors for formula fields dynamically. I don't know whether it is possible?

I have lost a lot of my time to sort out this formula but none was successful. Your kind help is appreciated.

Thank you in advance for your help.

Muzaffar