Adding hours/minutes to Date/Times
 

I posted a question similar to this not to long ago and it really helped me.

I am given the beginning dates/times of a service call and the amount of
hours and/or minutes it takes to complete the call. I need a formula to
calculate the ending dates/times of the call.

I need to exclude weekends and holidays. I do have a range of holiday dates
located on a different spreadsheet. The end times also need to be between 8
am and 5 pm.

Ive been trying to use the WORKDAY function and all other kinds of things
but I just cant get it to work.

Can somebody please help me?

Column A Column B Column C

Begin Date/Time Hours End Date/Time
08/11/2006 1:50 pm 4.4 08/14/2006 9:14 am
10/12/2006 8:34 am 13.2 10/13/2006 12:46 pm
10/12/2006 3:36 pm .4 10/12/2006 4:00 pm
08/30/2006 4:10 pm 18.8 09/01/2006 4:58 pm

Col A + Col B = Col C

Thank you so much.

Rachel

Adding hours/minutes to Date/Times
 

Hello Rachel,

you could try this formula in C2

=WORKDAY(A2,INT(B2/9)+(MROUND(MOD(A2+MOD(B2,9)/24,1),1/1440)17/24),holidays)-(MROUND(MOD(A2+MOD(B2,9)/24,1),1/1440)17/24)*9/24+MOD(A2+MOD(B2,9)/24,1)

where holidays is a named range containing your holiday dates.

Note: this assumes that the date/time in A2 is always within business hours,
perhaps this isn't the case?

"RFrechette" wrote:

I posted a question similar to this not to long ago and it really helped me.

I am given the beginning dates/times of a service call and the amount of
hours and/or minutes it takes to complete the call. I need a formula to
calculate the ending dates/times of the call.

I need to exclude weekends and holidays. I do have a range of holiday dates
located on a different spreadsheet. The end times also need to be between 8
am and 5 pm.

Ive been trying to use the WORKDAY function and all other kinds of things
but I just cant get it to work.

Can somebody please help me?

Column A Column B Column C

Begin Date/Time Hours End Date/Time
08/11/2006 1:50 pm 4.4 08/14/2006 9:14 am
10/12/2006 8:34 am 13.2 10/13/2006 12:46 pm
10/12/2006 3:36 pm .4 10/12/2006 4:00 pm
08/30/2006 4:10 pm 18.8 09/01/2006 4:58 pm

Col A + Col B = Col C

Thank you so much.

Rachel

Adding hours/minutes to Date/Times
 

YOU ARE AN ABSOLUTE ANGEL!

It worked perfectly. Thank you, thank you, thank you!

Rachel

"daddylonglegs" wrote:

Hello Rachel,

you could try this formula in C2

=WORKDAY(A2,INT(B2/9)+(MROUND(MOD(A2+MOD(B2,9)/24,1),1/1440)17/24),holidays)-(MROUND(MOD(A2+MOD(B2,9)/24,1),1/1440)17/24)*9/24+MOD(A2+MOD(B2,9)/24,1)

where holidays is a named range containing your holiday dates.

Note: this assumes that the date/time in A2 is always within business hours,
perhaps this isn't the case?

"RFrechette" wrote:

I posted a question similar to this not to long ago and it really helped me.

I am given the beginning dates/times of a service call and the amount of
hours and/or minutes it takes to complete the call. I need a formula to
calculate the ending dates/times of the call.

I need to exclude weekends and holidays. I do have a range of holiday dates
located on a different spreadsheet. The end times also need to be between 8
am and 5 pm.

Ive been trying to use the WORKDAY function and all other kinds of things
but I just cant get it to work.

Can somebody please help me?

Column A Column B Column C

Begin Date/Time Hours End Date/Time
08/11/2006 1:50 pm 4.4 08/14/2006 9:14 am
10/12/2006 8:34 am 13.2 10/13/2006 12:46 pm
10/12/2006 3:36 pm .4 10/12/2006 4:00 pm
08/30/2006 4:10 pm 18.8 09/01/2006 4:58 pm

Col A + Col B = Col C

Thank you so much.

Rachel