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]

the examples above the times are showing up when I copy/paste with the xl2bb as time values. Does the date come in as well? How is the text formatted?

 

[tigerzen]

the examples above the times are showing up when I copy/paste with the xl2bb as time values. Does the date come in as well? How is the text formatted?

When I go into Format numbers while on the cell it does say Time however it only shows 12:18 etc.
The original file contains cell entries like

2022-02-25T03:16:00+11:00​

I use Power Query to break it up into a cell containing 2022-02-25 and another cell containing what you see above which would be 03:16, the original file is in CSV format and requires cleaning up before it can be of any use.

 

[awoohaw]

okay, keeping the date and time together and in 24 hour format is beneficial. But, it does need to be a value. Although I do not not what the T means. But it looks like you are in a zone 11:00 hours different from UTC, is that before or after UTC I'm guessing it before? Is the threshold AM or PM?
What day is the threshold?

 

[tigerzen]

okay, keeping the date and time together and in 24 hour format is beneficial. But, it does need to be a value. Although I do not not what the T means. But it looks like you are in a zone 11:00 hours different from UTC, is that before or after UTC I'm guessing it before? Is the threshold AM or PM?
What day is the threshold?

The threshold could be either, in the example above it was PM, so the desired threshold could be either depending on whether we are examining pre or post midnight. The threshold calculation is probably secondary to the first requirement of determining whether the time is pre or post midnight. Perhaps I need to take that original cell 2022-02-25T03:16:00+11:00 and do something else with it so I can analyse further?

 

[awoohaw]

@tigerzen , my question about the threshold was not about AM/PM although that is a concern also.

I guess my overall concern is to remove ambiguity in worksheets. If you always use date/time values (formatted as TIME) and format with AM/PM or24 hr the ambiguity is mostly removed. You are starting with your raw csv file, so you have the ability from the onset of your file creation to do this.

The question I have is still valid and unanswered... how do you choose the date/time in the threshold cell? If it is your choice, the enter it as date/time value and your formulas are less complicated.

 

[awoohaw]

And, if you could share a few lines of the .csv and what threshold date/time you want to use the forum can post a context relevant solution.

 

[tigerzen]

And, if you could share a few lines of the .csv and what threshold date/time you want to use the forum can post a context relevant solution.

Hi again awoohaw, the threshold date/time is somewhat arbitrary and does depend a little on the situation, by far the most common threshold is midnight ie did something occur before or after midnight. Agree completely regarding the removal of ambiguity and makes sense to convert to values so no issue there, question is how to do it when the data arrives in the format it does which I'll share a segment of now.

13-4-2023.xlsx
	B
2	2022-02-20T07:15:00+11:00
3	2022-02-21T07:44:00+11:00
4	2022-02-22T08:00:00+11:00
5	2022-02-23T07:26:00+11:00
6	2022-02-24T03:49:00+11:00
7	2022-02-24T08:05:00+11:00
8	2022-02-25T07:39:00+11:00
9	2022-02-26T07:19:00+11:00
10	2022-02-27T06:54:00+11:00
11	2022-02-28T08:03:00+11:00
12	2022-03-01T09:09:00+11:00
13	2022-03-02T07:33:00+11:00
14	2022-03-03T09:58:00+11:00
15	2022-03-04T07:33:00+11:00
16	2022-03-05T09:36:00+11:00
17	2022-03-06T07:23:00+11:00
18	2022-03-07T09:16:00+11:00
19	2022-03-08T07:43:00+11:00
20	2022-03-09T07:24:00+11:00
21	2022-03-11T07:11:00+11:00
22	2022-03-12T07:42:00+11:00
23	2022-03-13T08:12:00+11:00
24	2022-03-14T08:09:00+11:00
25	2022-03-15T07:24:00+11:00
26	2022-03-16T07:45:00+11:00
27	2022-03-17T07:53:00+11:00
CSV

 

[awoohaw]

the times in the list do not go past 12:00, how do I know if the time is an AM or PM time? Do you have any over 12 in your entire file?

 

[tigerzen]

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