SOLVED

Formula to calculate a resolution date excluding weekends

%3CLINGO-SUB%20id%3D%22lingo-sub-1536077%22%20slang%3D%22en-US%22%3EFormula%20to%20calculate%20a%20resolution%20date%20excluding%20weekends%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536077%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20calculate%20a%20target%20date%20(%3CSTRONG%3ECase%20Resolution%20Date%3C%2FSTRONG%3E)%20in%20Excel.%3C%2FP%3E%3CP%3EOn%20my%20excel%20sheet%20I%20have%20columns%20called%3C%2FP%3E%3CP%3E%22%3CSTRONG%3EDate%2FTime%20Opened%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%22%3CSTRONG%3ETime%20Resolution%20hrs%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%22%3CSTRONG%3ETime%20Resolution%20Weekdays%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3EBasically%20I%20would%20like%20to%20calculate%20what%20would%20be%20the%20%22%3CSTRONG%3ECase%20Resolution%20Target%20Date%3C%2FSTRONG%3E%22.%20knowing%20that%20the%20date%20should%20only%20include%20weekdays%20(5%20days%20a%20week)%20with%208hrs%20business%20hours.%3C%2FP%3E%3CP%3EFor%20example%20A%20case%20with%26nbsp%3B%3C%2FP%3E%3CP%3EDate%2FTime%20Opened%3A%2028%2F02%2F2020%3C%2FP%3E%3CP%3Eand%20with%20a%20Time%20Resolution%20hrs%3A%20144%26nbsp%3B%3C%2FP%3E%3CP%3Ewill%20equal%20to%2018%20weekdays%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20Case%20Resolution%20Target%20Date%20will%20be%2026%2F03%2F2020%3C%2FP%3E%3CP%3EAs%20business%20hours%20are%208hrs.%3C%2FP%3E%3CP%3EBut%20I%20am%20not%20able%20to%20put%20a%20valid%20formula%20to%20calculate%26nbsp%3B%20Case%20Resolution%20Target%20Date%3C%2FP%3E%3CP%3ECould%20anyone%20help%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1536077%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536207%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calculate%20a%20resolution%20date%20excluding%20weekends%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735065%22%20target%3D%22_blank%22%3E%40hermy670%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20around%20function%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DWORKDAY.INTL(C2%2CINT(D2%2F8)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ethe%20question%20is%20when%20are%20weekends%20(Sat%2CSun%20or%20other%20days)%20and%20if%20Time%20Resolution%20Hrs%20is%20always%20multiplier%20of%208%20or%20not.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I am trying to calculate a target date (Case Resolution Date) in Excel.

On my excel sheet I have columns called

"Date/Time Opened"

"Time Resolution hrs"

"Time Resolution Weekdays"

Basically I would like to calculate what would be the "Case Resolution Target Date". knowing that the date should only include weekdays (5 days a week) with 8hrs business hours.

For example A case with 

Date/Time Opened: 28/02/2020

and with a Time Resolution hrs: 144 

will equal to 18 weekdays 

So the Case Resolution Target Date will be 26/03/2020

As business hours are 8hrs.

But I am not able to put a valid formula to calculate  Case Resolution Target Date

Could anyone help?

Thank you

1 Reply
Best Response confirmed by hermy670 (Occasional Visitor)
Solution

@hermy670 

That could be around function

=WORKDAY.INTL(C2,INT(D2/8),1)

the question is when are weekends (Sat,Sun or other days) and if Time Resolution Hrs is always multiplier of 8 or not.