Nesting Multiple Date and Time Functions

[User33]

Hello Excelians,

I have to calculate a 24hr SLA between date/ time Received in the following format of 0/0/00 0:00:00AM and date time Sent in the same format. ( Column A Received and Column B Sent) and Column D will display the time in between in the 37:00 hr format. A simple subtraction formula is fine but I need to integrate, possibly a Workday Funtion/ Workday Intl function or some other suggestion. I just can't seem to write the formula correctly. Can anyone help, I'm a lone island here since I handle this department by myself. SO I'd love some detailed feedback or template base formula if you know of one.

Recap: Time and date Sent - Time and date Received, excluding Sat and Sun. PLEASE HELP!

 

[rayk8301]

can you put example dates and your desired outcome and we'll get you the formula you need.

 

[User33]

can you put example dates and your desired outcome and we'll get you the formula you need.

Thank you SO much! Sure

Column B Column C Column D
3/4/2016 15:25PM 3/7/2016 9:17AM 65:52:00 (Current outcome)
17:52:00 (Desired Outcome)

So the 24 countdown should be from 15:25pm on Friday, exclude Sat and Sun and pick back up Monday @ midnight 12:00am.

Let me know if I'm not clarifying it correctly.

 

[rayk8301]

I get 17:52 by simply subtracting =3/7/16 9:17 - 3/4/16 15:25

do not add the pm/am...those are added automatically

Not sure why you're not getting that?

 

[User33]

I get 17:52 by simply subtracting =3/7/16 9:17 - 3/4/16 15:25

do not add the pm/am...those are added automatically

Not sure why you're not getting that?

No I've even tried it in a different work book with just this data and I still get 65:52 where it includes the weekend hours. I can manually add to the formula to subtract 48 hrs. But this is a report I don't want to have to adjust on every Friday entry.
I don't know why yours is coming out correct and mine is incorrect. When writing the formula are you =then clicking the cell or manually typing the date and time?

 

[User33]

Or maybe it's a version of excel that's different where yours is auto setup to recognize weekends and mine has to manually be written?

 

[rayk8301]

that's probably because your format isn't right....while you're on the cell that has 65:52, hit Ctrl+1, go down to custom, and make sure this is selected h:mm

you probably have [h]:mm selected.

 

[User33]

that's probably because your format isn't right....while you're on the cell that has 65:52, hit Ctrl+1, go down to custom, and make sure this is selected h:mm

you probably have [h]:mm selected.

You are %100 correct!!! MY oversight. I can't believe that. I'm 20% embarrassed but 80% grateful and relieved... THANK YOU for sticking this out with me! ????

 

[rayk8301]

welcome, glad it worked!