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!!!!

 

[SullyWYO]

Finding the difference between times is a lot more accurate if you include the date in the same cell as the time. Excel can correctly interpret the difference of an overnight shift if all of the data is in the same cell.

Once the data is in that structure, this formula should work: =IF(C<=B, A-B, A-C)

I assume that if someone clocks in after the shift start time, you want to calculate hours worked from the time they actually clocked in, rather than the shift start time (A-C instead of B-C).

 

[Micron]

I venture to say that in virtually 100% of cases, time span calculation problems involving different days go away when the values are date time and not just time. This should explain why.

The reason is that date time values are double precision floating point numbers that you can format in several ways to change the appearance of the data but not the data itself (notwithstanding that you can change the data type but that's not relevant here). So when I coerced Now() to its real number, I got
Cdbl(now()) = 44803.5262384259
The time portion is everything to the right of the decimal; date is to the left. Without the date, you will have issues subtracting. Using your aforementioned dates
Cdbl(#08/29/2022 11:00:00 PM#) = 44802.9583333333
cdbl(#08/30/2022 05:00:00 AM#) = 44803.2083333333

Subtract only using the time portions and you get useless values. However
#08/30/2022 05:00:00 AM# - #08/29/2022 11:00:00 PM# = 0.25 which is .25 of a day. To get time span in hours or minutes you have to do math. Hours is fairly simple:
(#08/30/2022 05:00:00 AM# - #08/29/2022 11:00:00 PM#)*24 = 6

 

[Cig12886]

This is all great info thank you! I guess I left out a huge variable, these values don’t change day to day. It’s a given period say Mon-Fri, ship is due in at 11:45pm and due out at 5:30am. My crew starts at 12:30am so I need excel to calculate how much time that ship is in my yard from 12:30am to 5:30am. The next ship may come in at 2:30am and out at 7am.

This schedule is good for a month so i can’t add a date associated with the inbound or outbound times.

 

[Micron]

I need excel to calculate how much time that ship is in my yard from 12:30am to 5:30am.

Then if you don't care that it arrived any time before 12:30, use 12:30:00 AM as the in time and the out time is anything up to 05:30:00 AM but not later? Or do I still not get the situation? So maybe like (formula in column 3 below is in K, result is 0.20833)
=IF(J3 > TIMEVALUE("05:30:00 AM"),TIMEVALUE("05:30:00 AM") - TIMEVALUE("12:30:00 am"),J3-I3)

12:30:00 AM​

7:30:00 AM​

0.208333​

5​

I have no idea what your data looks like so maybe I used the wrong functions. I used =K3*24 to convert to hours. The answer was 5 regardless if the out time was 5:30 am or 7:30 am.

 

[Cig12886]

Then if you don't care that it arrived any time before 12:30, use 12:30:00 AM as the in time and the out time is anything up to 05:30:00 AM but not later? Or do I still not get the situation? So maybe like (formula in column 3 below is in K, result is 0.20833)
=IF(J3 > TIMEVALUE("05:30:00 AM"),TIMEVALUE("05:30:00 AM") - TIMEVALUE("12:30:00 am"),J3-I3)

12:30:00 AM​

7:30:00 AM​

0.208333​

5​

I have no idea what your data looks like so maybe I used the wrong functions. I used =K3*24 to convert to hours. The answer was 5 regardless if the out time was 5:30 am or 7:30 am.

I really may have over thank this whole thing. You’re exactly right. if I don’t care that it arrives before the start of the shift at 12:30am I can use that as a constant to determine the dwell time.

When I get back to the office tomorrow I will double check my work & share my data.

Thanks for the help/clarity

 

[Cig12886]

I really may have over thank this whole thing. You’re exactly right. if I don’t care that it arrives before the start of the shift at 12:30am I can use that as a constant to determine the dwell time.

When I get back to the office tomorrow I will double check my work & share my data.

Thanks for the help/clarity

as i sit here it doesn't work out in my head. i need a formula that will look at the inbound times sometimes before or after 12:30am then decide if it will calculate the dwell time off of 12:30am when the ships come in prior to the start of the shift or from its inbound time during the shift.

Shift start 12:30 am, Shift ends 8:30am, with that info the dwell time should be what i have entered in the column & that's what i need a formula for. Once that is established i can manipulate for 8:30am-4:30pm & 4:30pm-12:30am shift.

Inbound Time	Outbound Time	Dwell Time
11:00 pm	3:30 am	3hrs
12:30 am	7:47 am	7:17hrs
2:25 am	9:25 am	7hrs
10:35 pm	2:00 pm	8hrs

 

[Micron]

Given

column I value: 11:00 PM​

column J value: 2:30 AM​

column K result: 2​

in K: =IF(I6>J6, J6-TIMEVALUE("12:30:00 am"),J6-I6)*24

Expect Excel to screw up the formatting, so don't judge the results until you correctly set them.
Date cells are formatted custom: h:mm AM/PM. K is formatted General (which tended to get altered on me as I played with the formula).
The formula says: if start>end (it's before midnight) then subtract timevalue of 12:30:00 am from end time, otherwise subtract start from end. Multiply result by 24 to get hours.

I tested with a few values before and after 12:30 am. Seems OK. You could try it without the timevalue function to see if it still works. Should have been a lot simpler than the time I spent on it, but formulas in Excel are not my thing.

EDIT - you may still have an issue if you enter start times like 12:00 am. No idea what you'd do about that.

 

[Micron]

you may still have an issue if you enter start times like 12:00 am. No idea what you'd do about that.

hmmm, maybe =IF(OR(I3>J3,I3<TIMEVALUE("12:30 am")),J3-TIMEVALUE("12:30 am"),J3-I3)*24

I faked the headers, so the 1st row is really row 3 in my data.
It looks funny because column I suggests the calculations are wrong when those values are before 12:30 am

I	J	K
2:30:00 AM​	7:30:00 AM​	5​
12:00:00 AM​	3:30:00 AM​	3​
12:30:00 AM​	6:30:00 AM​	6​
12:00:00 AM​	2:30:00 AM​	2​
11:30:00 PM​	2:30:00 AM​	2​
12:30 AM​	5:00 AM​	4.5​

 

[Cig12886]

=IF(OR(I3>J3,I3<TIMEVALUE("12:30 am")),J3-TIMEVALUE("12:30 am"),J3-I3)*24

this worked!!! thanks for your help!

Correct me if I am wrong, if i want to change the shift start time I would just change the Timevalue to 4:30pm or 8:30am correct?