Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
My discovery on adding months and days to a date! Epinn Excel Worksheet Functions 8 October 9th 06 10:27 PM
building YTD figures by adding to a previous months total axialtilt Excel Discussion (Misc queries) 7 July 24th 06 04:07 PM
adding days jcool12 Excel Discussion (Misc queries) 4 June 14th 06 09:25 AM
Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? StargateFan Excel Discussion (Misc queries) 8 January 7th 06 08:35 PM
help neede with adding times rvnwdr Excel Discussion (Misc queries) 1 June 17th 05 02:15 PM


All times are GMT +1. The time now is 04:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"