Day & Time Calculation - Excluding Weekends

[fareedexcel]

Hi,

I need to calculate date and Time from a start date and what will be their end date?

Attachment Sample for reference.

 

[pecoflyer]

Try the WORKDAY.INTL function

 

[fareedexcel]

Try the WORKDAY.INTL function

I tried. But I need that start date +1.875 as my end date. so if thursday is the start date. end date should be monday morning 9am. i need with both date and time

 

[bosco_yip]

Try,

In D5, copied down :

=C5+MID(1111332,MOD(C5,7)+0.5,1)+0.875

Regards
Bosco

 

[fareedexcel]

Thanks Bosco. The formula is working absolutely perfect. May I know the logic behind the formula. Mid (1111332)?

 

[GraH - Guido]

I tried. But I need that start date +1.875 as my end date. so if thursday is the start date. end date should be monday morning 9am. i need with both date and time

Though it seems to work like this
[D5]=WORKDAY.INTL(C5,2,7)+TIME(9,,)

 

[GraH - Guido]

Thanks Bosco. The formula is working absolutely perfect. May I know the logic behind the formula. Mid (1111332)?

I guess basically it is the number of days you add based on the day of week, the position in the string, that is calculated with mod function (see you divide by 7).
Got to get more into details to completely get it. You'll have that with Bosco's wit.

 

[bosco_yip]

Thanks Bosco. The formula is working absolutely perfect. May I know the logic behind the formula. Mid (1111332)?

Maybe this working table picture can explain Mid(1111332........), please see Column E highlighted in red color number.

p.s. yellow colored cells is the OP's example data

Regards
Bosco

 

[fareedexcel]

Though it seems to work like this
[D5]=WORKDAY.INTL(C5,2,7)+TIME(9,,)

This formula is working fine. I have a scenario what if the time changes in start date. for example i need the end date after 11 hours from the start date excluding weekends, (working hours - 7am to 4 pm) - 9 hrs per day.

I slightly tweaked the formula,

[d5]=workday.intl(c5,1,7)+time(14,,)

so if the date is 4.11.2018 12pm then end date is 5.11.2018 2 pm

but when i tried changing the time in start date 4.11.2018 1pm, then end date is showing as same 5.11.2018 2pm. here the time should change as 5.11.2018 3pm.

if the start date is 4.11.2018 3pm then end date is 5.11.2018 8am. (total 11 working hours) plus if any weekends (Fri,sat) to be excluded.

 

[fareedexcel]

View attachment 56302

Maybe this working table picture can explain Mid(1111332........), please see Column E highlighted in red color number.

p.s. yellow colored cells is the OP's example data

Regards
Bosco

Thanks Bosco for the reply. Can u help me with the above question.There is slight time difference in my end date calculation.