Time Difference Calculation

[Cig12886]

I am having an issue calculating time, I need to calculate the time between the start of the shift at 12:30am and various release times. However, sometimes the in time is prior to the start of the shift so I need the formula to look at the in time and decide if it is before or after the 12:30am start.

In English this is what I came up with:

If the in time (C) is less than or equal to the shift start time of 12:30am (B) then calculate the difference between 12:30am (B) & the release time (A). If the in time (c) is greater than the shift start time of 12:30am (b) then find the difference between the in time (c) & release time (a).

Formula I tried to work was:

=IF(C<=B, A-B, B-C)

This somewhat worked, it gave me trouble calculating from the day prior i.e 11pm to 5am. Then I would manipulate the formula to include two if clauses & I still couldn't get it to work.

example of data being inputted

In @ 12:30 am, Out 9:00 am
In @ 12:45 am, Out 6:00 am
In @ 10:00 pm, Out at 4:00 am

Any help would be appreciated!!!!

 

[Micron]

Yes, that should do it. If you consider this solved, could you mark it as solved so that other people who are looking for threads to help with don't waste any time reading it all? Thanks.

 

[Cig12886]

I just got around to working on my second shift layout (4:30pm-12:30am). The formula works until the out time is after 11:59pm, once the out time is after 12:00am it gives you the wrong dwell time. Any thoughts?

 

[Micron]

I just got around to working on my second shift layout (4:30pm-12:30am). The formula works until the out time is after 11:59pm, once the out time is after 12:00am it gives you the wrong dwell time. Any thoughts?

Yes. Smartest thing? Give in and incorporate dates and this and any future problems with time spans will go away. Not as smart: create another sheet and modify the formula to work with this situation. I can honestly say I foresaw this but did not it mention because I thought it to be irrelevant given the parameters you provided.

 

[Cig12886]

i wish i could incorporate dates but that will cause anarchy on my end. would you be able to help with the formula for this particular situation? i was thinking i need to add another if statement, something such as if the outbound time is after 11:59pm then calculate the dwell time somehow

This is the formula I have been using for the other shifts, I added an if statement for blank cells so it didnt show erroneous values when an inbound & outbound time weren't entered
=IF(AND(C17<>"",D17<>""),IF(OR(C17>D17,C17<TIMEVALUE("4:30 pm")),D17-TIMEVALUE("4:30 pm"),D17-C17)*24,"")

 

[Micron]

No, sorry. Excel formulas are not my thing and I spent considerable time on this problem & coming up with what I posted, which doesn't look anything like how it has evolved. You've had 2 responders state that your issue wouldn't be an issue if you had dates. Repeating that you can't doesn't motivate me to continue thrashing around with hacks to fix what shouldn't be a problem in the first place. If it's your bosses that are resisting dates, perhaps you should tell them what you've been told and ask them if they want you to spend hours trying to solve this in other ways - ways that will only fail every time some new wrinkle is introduced. All I can suggest beyond what I've already suggested is to use the formula evaluation tool to see the step by step results of what you create if you're not going to split this into 2 problems (2 sheets). You have a problem when your out time is 12:00 AM because technically you've moved on to another day. That causes the right side of your end time date/time value (right side of the decimal) to start over at zero. Dates will solve that.