Time calculations

[tigerzen]

Need to work out what I thought was simple enough but basically need to return if an incident occurred after midnight. Also a formula to calculate the difference between the time of the incident compared to a selected threshold time eg if the threshold time was 11:30 pm then how long after that was the incident? I've included required output to help explain the situation. The problem is that the data comes in with the time as text and doesn't allow any meaningful calculations. The threshold time is to be set dynamically.

Book1
	A	B	C	D	E	F	G
1	Staff	Time occurred	After midnight	How long after Threshold		Threshold
2	Friday	12:18
3	Thursday	10:50
4	Wednesday	10:47
5	Tuesday	11:31
6	Monday	10:32
7	Sunday	11:48
8	Saturday	1:41
9
10	Required ouptut
11	Staff	Time occurred	After midnight	How long after Threshold		Threshold	11:45
12	Friday	12:18	Yes	33
13	Thursday	10:50
14	Wednesday	10:47
15	Tuesday	11:31
16	Monday	10:32
17	Sunday	11:48		3
18	Saturday	1:41	Yes	116
Sheet1

Cell Formulas
Range	Formula
D12	D12	=33

 

[awoohaw]

I'll post another sample which is more relevant to the issue. Shifts begin from 10 pm onto 6 am, please ignore the previous CSV data, on this one anything 22 or 23 would be 10 pm and 11 pm respectively. Anything else eg 01:34 would be 1:34 am.

13-4-2023.xlsx
	A
2	2022-02-19T23:58:00+11:00
3	2022-02-21T00:23:00+11:00
4	2022-02-21T23:30:00+11:00
5	2022-02-23T01:32:00+11:00
6	2022-02-23T23:49:00+11:00
7	2022-02-23T23:49:00+11:00
8	2022-02-25T03:16:00+11:00
9	2022-02-25T20:11:00+11:00
10	2022-02-27T00:02:00+11:00
11	2022-02-27T23:23:00+11:00
12	2022-03-01T00:40:00+11:00
13	2022-03-02T03:45:00+11:00
14	2022-03-03T05:29:00+11:00
15	2022-03-04T00:10:00+11:00
16	2022-03-04T23:36:00+11:00
17	2022-03-06T00:39:00+11:00
18	2022-03-07T00:55:00+11:00
19	2022-03-07T22:58:00+11:00
20	2022-03-08T23:27:00+11:00
21	2022-03-11T00:43:00+11:00
22	2022-03-12T00:27:00+11:00
23	2022-03-13T00:18:00+11:00
24	2022-03-14T00:04:00+11:00
25	2022-03-14T23:11:00+11:00
26	2022-03-15T23:32:00+11:00
27	2022-03-16T23:21:00+11:00
CSV

okay, and should we ignore the +11? Is that saying you are in time zone +11 or that you need to add 11 to get your correct time?

 

[awoohaw]

this is assuming the time portion of the csv is the time in your time zone. (I did not add the +11:00:00)
It is so much easier when you use entire date-time values. Please look at what value is in cell H1.
if your threshold value needs to change, then I suggest putting that in a column of its own and manually changing it as you see fit. Otherwise any prior calculations will be corrupted when you make subsequent changes to the threshold (unless you copy/paste those prior calculations to values after they are completed, which is risky as you would cause errors if you forget to do that).

mr excel questions 24.xlsm
	A	B	C	D	E	F	G	H
1	csv time stamp	Staff	Time occurred	After midnight	How long after Threshold		Threshold	2022-02-19 23:45
2	2022-02-19T23:58:00+11:00	Saturday	23:58		00:13
3	2022-02-21T00:23:00+11:00	Monday	00:23		24:38
4	2022-02-21T23:30:00+11:00	Monday	23:30		47:45
5	2022-02-23T01:32:00+11:00	Wednesday	01:32		73:47
6	2022-02-23T23:49:00+11:00	Wednesday	23:49		96:04
7	2022-02-23T23:49:00+11:00	Wednesday	23:49		96:04
8	2022-02-25T03:16:00+11:00	Friday	03:16		123:31
tigerzen

Cell Formulas
Range	Formula
H1	H1	=DATE(2022,2,19)+TIME(23,45,0)
B2:C8	B2	=DATEVALUE(TEXTBEFORE($A2,"T"))+TIMEVALUE(TEXTBEFORE(TEXTAFTER($A2,"T"),"+"))
E2:E8	E2	=C2-$H$1

 

[tigerzen]

The +11 needs to be ignored.

 

[awoohaw]

The +11 needs to be ignored.

okay, then the calculation i have above should be what you need. I hope it makes sense.

 

[tigerzen]

Thanks awoohaw, I think it's close but column E is the difference in time, the days should not be included in the calculation, so cell E3 should be 38 mins.

 

[awoohaw]

i'm confused. 11:45 to 11:58 is 13 minutes. Can you show me what numbers (cells and their values) are supposed to be compared?

at what values are expected in E4:E9?

And i'm not sure what you mean by excluding days (dates). I did not see (It may be there, but I didn't see) any requirement regarding that.
I'm happy to address that but, I need more explanation of your desired output,

Thanks awoohaw, I think it's close but column E is the difference in time, the days should not be included in the calculation, so cell E3 should be 38 min

 

[awoohaw]

Thanks awoohaw, I think it's close but column E is the difference in time, the days should not be included in the calculation, so cell E3 should be 38 mins.

My experience is not the defacto convention of doing things. But Dates are in-extricably asscociated with time, especially when you are calculating time differences over a midnight -I absolutely need to have a day in the calculation (even if it is 1/1/1900 1:00 AM compared to a start time on 1/0/1900).

 

[tigerzen]

i'm confused. 11:45 to 11:58 is 13 minutes. Can you show me what numbers (cells and their values) are supposed to be compared?

at what values are expected in E4:E9?

And i'm not sure what you mean by excluding days (dates). I did not see (It may be there, but I didn't see) any requirement regarding that.
I'm happy to address that but, I need more explanation of your desired output,

In your post #12 above, the difference should be 38 mins in E3 but you have 24:38, it comes down to whether you include the days transpired rather than just the time but I think one of your subsequent posts might explain this.

 

[awoohaw]

In your post #12 above, the difference should be 38 mins in E3 but you have 24:38, it comes down to whether you include the days transpired rather than just the time but I think one of your subsequent posts might explain this.

yes, you will likely run into this problem regardless if the threshold time is a time on day zero or a date-time value. What ever you subtract this time from you will have a difference with the date time of the row (unless you only use a time, but then you will miss the beyond midnight calculations or have to include more calculations in it).