Issue with 00:00 time when using an IF function

[Charles_P]

Can anyone help me solve this?

I am trying to use a simple IF function to display which shift is working based on a series of time entries. I am working with a UTC time set, but I need to adjust the times to be UTC+1, to use this I am using a simple addition calculation. However, when I apply the IF function to the adjusted (UTC+1) time entries, it returns the same results as the UTC time set around 00:00, but outputs correctly around 12:00 (the shift change times). If I manually type in the adjusted times, the IF function works as expected. What is going on?

The functions being used are;

• =A2+(1/24) Where A2 is UTC time - I have tried other methods of adding 1hr but all return the same result as described above, so I do not think that this is where the issue lies.
• =IF(A2>(12/24),"DAYS","NIGHTS") Where A2 is UTC time - This works as expected
• =IF(B2>(12/24),"DAYS","NIGHTS") Where B2 is UTC+1 time - reruns the same result as UTC time around 00:00, but correctly adjusts around 12:00 - Again, I have tried other forms of writing 12:00 within the function, but all yield the same outcome around 00:00.
• =IF(E8="","",IF(E8>(12/24),"DAYS","NIGHTS")) Where E8 is manually written time and this works as expected - Obviously the first IF function is there just to keep things tidy - I have tired without this and there is the same correct outcome.

I have included a sample of the dataset and the formulas actively being applied.

Thank you in advance,

Charles

IF Function 0000 Time Error.xlsx
	A	B	C	D	E	F
1	UTC TIME	UTC+1	Shift UTC	SHIFT UTC+1	Manually typed UTC+1 Time	SHIFT UTC+1
2	19:17:19	20:17:19	DAYS	DAYS
3	19:37:37	20:37:37	DAYS	DAYS
4	20:08:14	21:08:14	DAYS	DAYS
5	20:31:30	21:31:30	DAYS	DAYS
6	20:51:45	21:51:45	DAYS	DAYS
7	22:14:51	23:14:51	DAYS	DAYS
8	22:38:23	23:38:23	DAYS	DAYS	23:38:23	DAYS
9	22:58:08	23:58:08	DAYS	DAYS	23:58:08	DAYS
10	23:20:31	0:20:31	DAYS	DAYS	00:21:31	NIGHTS
11	0:10:27	1:10:27	NIGHTS	NIGHTS	01:10:27	NIGHTS
12	0:39:30	1:39:30	NIGHTS	NIGHTS
13	1:28:29	2:28:29	NIGHTS	NIGHTS
14	2:05:33	3:05:33	NIGHTS	NIGHTS
15	2:32:18	3:32:18	NIGHTS	NIGHTS
16	2:57:50	3:57:50	NIGHTS	NIGHTS
17	3:27:43	4:27:43	NIGHTS	NIGHTS
18	4:01:54	5:01:54	NIGHTS	NIGHTS
19	4:34:09	5:34:09	NIGHTS	NIGHTS
20	4:56:35	5:56:35	NIGHTS	NIGHTS
21	5:18:42	6:18:42	NIGHTS	NIGHTS
22	5:46:18	6:46:18	NIGHTS	NIGHTS
23	6:03:05	7:03:05	NIGHTS	NIGHTS
24	6:25:08	7:25:08	NIGHTS	NIGHTS
25	6:48:53	7:48:53	NIGHTS	NIGHTS
26	7:09:02	8:09:02	NIGHTS	NIGHTS
27	7:30:27	8:30:27	NIGHTS	NIGHTS
28	7:51:07	8:51:07	NIGHTS	NIGHTS
29	8:10:22	9:10:22	NIGHTS	NIGHTS
30	9:04:10	10:04:10	NIGHTS	NIGHTS
31	9:22:25	10:22:25	NIGHTS	NIGHTS
32	9:30:21	10:30:21	NIGHTS	NIGHTS
33	9:35:38	10:35:38	NIGHTS	NIGHTS
34	9:41:49	10:41:49	NIGHTS	NIGHTS
35	9:49:46	10:49:46	NIGHTS	NIGHTS
36	9:55:23	10:55:23	NIGHTS	NIGHTS
37	10:03:13	11:03:13	NIGHTS	NIGHTS
38	10:09:47	11:09:47	NIGHTS	NIGHTS
39	10:17:23	11:17:23	NIGHTS	NIGHTS
40	10:25:27	11:25:27	NIGHTS	NIGHTS
41	10:33:47	11:33:47	NIGHTS	NIGHTS
42	14:03:03	15:03:03	DAYS	DAYS
43	14:16:39	15:16:39	DAYS	DAYS
44	14:28:58	15:28:58	DAYS	DAYS
45	14:37:23	15:37:23	DAYS	DAYS
46	14:49:53	15:49:53	DAYS	DAYS
47	15:01:34	16:01:34	DAYS	DAYS
48	15:14:49	16:14:49	DAYS	DAYS
49	15:29:43	16:29:43	DAYS	DAYS
50	15:43:07	16:43:07	DAYS	DAYS
51	15:55:15	16:55:15	DAYS	DAYS
52	16:07:42	17:07:42	DAYS	DAYS
53	16:21:43	17:21:43	DAYS	DAYS
54	16:36:38	17:36:38	DAYS	DAYS
55	16:51:33	17:51:33	DAYS	DAYS
56	17:26:37	18:26:37	DAYS	DAYS
57	17:44:39	18:44:39	DAYS	DAYS
58	18:06:01	19:06:01	DAYS	DAYS
59	18:31:00	19:31:00	DAYS	DAYS	19:31:00	DAYS
60	23:20:12	0:20:12	DAYS	DAYS	00:20:12	NIGHTS
61	23:48:36	0:48:36	DAYS	DAYS	00:48:36	NIGHTS
62	0:02:51	1:02:51	NIGHTS	NIGHTS	01:02:51	NIGHTS
63	0:17:29	1:17:29	NIGHTS	NIGHTS	01:17:29	NIGHTS
64	0:35:04	1:35:04	NIGHTS	NIGHTS
65	0:50:21	1:50:21	NIGHTS	NIGHTS
66	1:32:36	2:32:36	NIGHTS	NIGHTS
67	17:49:57	18:49:57	DAYS	DAYS
68	18:03:44	19:03:44	DAYS	DAYS
69	18:15:41	19:15:41	DAYS	DAYS
70	18:27:51	19:27:51	DAYS	DAYS
71	18:43:11	19:43:11	DAYS	DAYS
72	18:54:19	19:54:19	DAYS	DAYS
73	19:04:58	20:04:58	DAYS	DAYS
74	19:19:01	20:19:01	DAYS	DAYS
75	19:32:07	20:32:07	DAYS	DAYS
76	19:46:11	20:46:11	DAYS	DAYS
77	20:14:51	21:14:51	DAYS	DAYS
78	22:17:29	23:17:29	DAYS	DAYS
79	22:28:03	23:28:03	DAYS	DAYS	23:28:03	DAYS
80	22:46:37	23:46:37	DAYS	DAYS	23:46:37	DAYS
81	23:11:42	0:11:42	DAYS	DAYS	00:11:42	NIGHTS
82	23:26:38	0:26:38	DAYS	DAYS	00:26:38	NIGHTS
83	23:35:48	0:35:48	DAYS	DAYS	00:35:48	NIGHTS
84	23:48:30	0:48:30	DAYS	DAYS	00:48:30	NIGHTS
85	0:00:45	1:00:45	NIGHTS	NIGHTS	01:00:45	NIGHTS
86	0:14:29	1:14:29	NIGHTS	NIGHTS
87	0:28:44	1:28:44	NIGHTS	NIGHTS
88	0:43:21	1:43:21	NIGHTS	NIGHTS
89	0:56:18	1:56:18	NIGHTS	NIGHTS
90	1:12:51	2:12:51	NIGHTS	NIGHTS	02:12:51	NIGHTS
91	1:25:35	2:25:35	NIGHTS	NIGHTS	02:22:35	NIGHTS
92	23:35:56	0:35:56	DAYS	DAYS	00:35:56	NIGHTS
93	23:54:35	0:54:35	DAYS	DAYS	00:54:35	NIGHTS
94	0:22:01	1:22:01	NIGHTS	NIGHTS	01:22:01	NIGHTS
95	0:48:10	1:48:10	NIGHTS	NIGHTS	01:48:10	NIGHTS
96	0:59:44	1:59:44	NIGHTS	NIGHTS
97	1:10:24	2:10:24	NIGHTS	NIGHTS
98	1:18:37	2:18:37	NIGHTS	NIGHTS
99	1:36:55	2:36:55	NIGHTS	NIGHTS
100	6:06:37	7:06:37	NIGHTS	NIGHTS
101	6:15:27	7:15:27	NIGHTS	NIGHTS
102	6:23:59	7:23:59	NIGHTS	NIGHTS
103	6:33:56	7:33:56	NIGHTS	NIGHTS
104	6:43:36	7:43:36	NIGHTS	NIGHTS
105	6:52:20	7:52:20	NIGHTS	NIGHTS
106	7:01:17	8:01:17	NIGHTS	NIGHTS
107	7:08:28	8:08:28	NIGHTS	NIGHTS
108	7:17:58	8:17:58	NIGHTS	NIGHTS
109	10:28:08	11:28:08	NIGHTS	NIGHTS	11:28:08	NIGHTS
110	10:51:22	11:51:22	NIGHTS	NIGHTS	11:51:22	NIGHTS
111	11:40:47	12:40:47	NIGHTS	DAYS	12:40:47	DAYS
112	11:50:21	12:50:21	NIGHTS	DAYS	12:50:21	DAYS
113	11:55:18	12:55:18	NIGHTS	DAYS	12:55:18	DAYS
114	12:20:40	13:20:40	DAYS	DAYS	13:20:40	DAYS
115	12:31:09	13:31:09	DAYS	DAYS
116	12:40:54	13:40:54	DAYS	DAYS
117	12:50:45	13:50:45	DAYS	DAYS
118	12:58:34	13:58:34	DAYS	DAYS
119	13:08:09	14:08:09	DAYS	DAYS
120	13:15:46	14:15:46	DAYS	DAYS
121
Sheet1

Cell Formulas
Range	Formula
B2:B120	B2	=A2+(1/24)
C2:D120	C2	=IF(A2>(12/24),"DAYS","NIGHTS")
F2:F120	F2	=IF(E2="","",IF(E2>(12/24),"DAYS","NIGHTS"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=$G1="Incomplete"	text	NO
A1	Expression	=$G1="Complete"	text	NO

 

[jtakw]

Hi,

You added a day when you add 1 hour to times crossing midnight, change your B Column formula to:

=MOD(A2+(1/24),1)

Copied down.

 

[Charles_P]

Thank you for the quick response! I thought I was going mad, but that actually makes perfect sense!

Thank you again!

 

[jtakw]

You're welcome, thanks for the feedback, glad to help.