Date and Time for one month for 24 hours in one column

[suyah]

I have difficulties in date and time format. Since I always need to change the day/month/year. Usually I just did it manually, however after I found this amazing macro things, I hope I can do it using macro which can save me a lot of time.

Here the situation is:

I have 16 years (1996-2011) data to do this which in each years have 12 month, in one month which have different days (28/29/30/31) and each day 24 hours. I want to write it in one column for one month.

Example of result:

date
01/04/1996 01:00:00
01/04/1996 02:00:00
01/04/1996 03:00:00
01/04/1996 04:00:00
01/04/1996 05:00:00
01/04/1996 06:00:00
01/04/1996 07:00:00
01/04/1996 08:00:00
01/04/1996 09:00:00
01/04/1996 10:00:00
01/04/1996 11:00:00
01/04/1996 12:00:00
01/04/1996 13:00:00
01/04/1996 14:00:00
01/04/1996 15:00:00
01/04/1996 16:00:00
01/04/1996 17:00:00
01/04/1996 18:00:00
01/04/1996 19:00:00
01/04/1996 20:00:00
01/04/1996 21:00:00
01/04/1996 22:00:00
01/04/1996 23:00:00
01/04/1996 00:00:00
02/04/1996 01:00:00
02/04/1996 02:00:00
02/04/1996 03:00:00
02/04/1996 04:00:00
02/04/1996 05:00:00
02/04/1996 06:00:00
02/04/1996 07:00:00
02/04/1996 08:00:00
02/04/1996 09:00:00
02/04/1996 10:00:00
02/04/1996 11:00:00
02/04/1996 12:00:00
02/04/1996 13:00:00
02/04/1996 14:00:00
02/04/1996 15:00:00
02/04/1996 16:00:00
02/04/1996 17:00:00
02/04/1996 18:00:00
02/04/1996 19:00:00
02/04/1996 20:00:00
02/04/1996 21:00:00
02/04/1996 22:00:00
02/04/1996 23:00:00
02/04/1996 00:00:00
03/04/1996 01:00:00
03/04/1996 02:00:00
03/04/1996 03:00:00
03/04/1996 04:00:00
03/04/1996 05:00:00
03/04/1996 06:00:00
03/04/1996 07:00:00
03/04/1996 08:00:00
03/04/1996 09:00:00
03/04/1996 10:00:00
03/04/1996 11:00:00
03/04/1996 12:00:00
03/04/1996 13:00:00
03/04/1996 14:00:00
03/04/1996 15:00:00
03/04/1996 16:00:00
03/04/1996 17:00:00
03/04/1996 18:00:00
03/04/1996 19:00:00
03/04/1996 20:00:00
03/04/1996 21:00:00
03/04/1996 22:00:00
03/04/1996 23:00:00
03/04/1996 00:00:00
04/04/1996 01:00:00
04/04/1996 02:00:00
04/04/1996 03:00:00
04/04/1996 04:00:00
04/04/1996 05:00:00
04/04/1996 06:00:00
04/04/1996 07:00:00
04/04/1996 08:00:00
04/04/1996 09:00:00
04/04/1996 10:00:00
04/04/1996 11:00:00
04/04/1996 12:00:00
04/04/1996 13:00:00
04/04/1996 14:00:00
04/04/1996 15:00:00
04/04/1996 16:00:00
04/04/1996 17:00:00
04/04/1996 18:00:00
04/04/1996 19:00:00
04/04/1996 20:00:00
04/04/1996 21:00:00
04/04/1996 22:00:00
04/04/1996 23:00:00
04/04/1996 00:00:00
05/04/1996 01:00:00
05/04/1996 02:00:00
05/04/1996 03:00:00
05/04/1996 04:00:00
05/04/1996 05:00:00
05/04/1996 06:00:00
05/04/1996 07:00:00
05/04/1996 08:00:00
05/04/1996 09:00:00
05/04/1996 10:00:00
05/04/1996 11:00:00
05/04/1996 12:00:00
05/04/1996 13:00:00
05/04/1996 14:00:00
05/04/1996 15:00:00
05/04/1996 16:00:00
05/04/1996 17:00:00
05/04/1996 18:00:00
05/04/1996 19:00:00
05/04/1996 20:00:00
05/04/1996 21:00:00
05/04/1996 22:00:00
05/04/1996 23:00:00
05/04/1996 00:00:00
06/04/1996 01:00:00
06/04/1996 02:00:00
06/04/1996 03:00:00
06/04/1996 04:00:00
06/04/1996 05:00:00
06/04/1996 06:00:00
06/04/1996 07:00:00
06/04/1996 08:00:00
06/04/1996 09:00:00
06/04/1996 10:00:00
06/04/1996 11:00:00
06/04/1996 12:00:00
06/04/1996 13:00:00
06/04/1996 14:00:00
06/04/1996 15:00:00
06/04/1996 16:00:00
06/04/1996 17:00:00
06/04/1996 18:00:00
06/04/1996 19:00:00
06/04/1996 20:00:00
06/04/1996 21:00:00
06/04/1996 22:00:00
06/04/1996 23:00:00
06/04/1996 00:00:00
07/04/1996 01:00:00
07/04/1996 02:00:00
07/04/1996 03:00:00
07/04/1996 04:00:00
07/04/1996 05:00:00
07/04/1996 06:00:00
07/04/1996 07:00:00
07/04/1996 08:00:00
07/04/1996 09:00:00
07/04/1996 10:00:00
07/04/1996 11:00:00
07/04/1996 12:00:00
07/04/1996 13:00:00
07/04/1996 14:00:00
07/04/1996 15:00:00
07/04/1996 16:00:00
07/04/1996 17:00:00
07/04/1996 18:00:00
07/04/1996 19:00:00
07/04/1996 20:00:00
07/04/1996 21:00:00
07/04/1996 22:00:00
07/04/1996 23:00:00
07/04/1996 00:00:00
08/04/1996 01:00:00
08/04/1996 02:00:00
08/04/1996 03:00:00
08/04/1996 04:00:00
08/04/1996 05:00:00
08/04/1996 06:00:00
08/04/1996 07:00:00
08/04/1996 08:00:00
08/04/1996 09:00:00
08/04/1996 10:00:00
08/04/1996 11:00:00
08/04/1996 12:00:00
08/04/1996 13:00:00
08/04/1996 14:00:00
08/04/1996 15:00:00
08/04/1996 16:00:00
08/04/1996 17:00:00
08/04/1996 18:00:00
08/04/1996 19:00:00
08/04/1996 20:00:00
08/04/1996 21:00:00
08/04/1996 22:00:00
08/04/1996 23:00:00
08/04/1996 00:00:00
09/04/1996 01:00:00
09/04/1996 02:00:00
09/04/1996 03:00:00
09/04/1996 04:00:00
09/04/1996 05:00:00
09/04/1996 06:00:00
09/04/1996 07:00:00
09/04/1996 08:00:00
09/04/1996 09:00:00
09/04/1996 10:00:00
09/04/1996 11:00:00
09/04/1996 12:00:00
09/04/1996 13:00:00
09/04/1996 14:00:00
09/04/1996 15:00:00
09/04/1996 16:00:00
09/04/1996 17:00:00
09/04/1996 18:00:00
09/04/1996 19:00:00
09/04/1996 20:00:00
09/04/1996 21:00:00
09/04/1996 22:00:00
09/04/1996 23:00:00
09/04/1996 00:00:00
10/04/1996 01:00:00
10/04/1996 02:00:00
10/04/1996 03:00:00
10/04/1996 04:00:00
10/04/1996 05:00:00
10/04/1996 06:00:00
10/04/1996 07:00:00
10/04/1996 08:00:00
10/04/1996 09:00:00
10/04/1996 10:00:00
10/04/1996 11:00:00
10/04/1996 12:00:00
10/04/1996 13:00:00
10/04/1996 14:00:00
10/04/1996 15:00:00
10/04/1996 16:00:00
10/04/1996 17:00:00
10/04/1996 18:00:00
10/04/1996 19:00:00
10/04/1996 20:00:00
10/04/1996 21:00:00
10/04/1996 22:00:00
10/04/1996 23:00:00
10/04/1996 00:00:00
11/04/1996 01:00:00
11/04/1996 02:00:00
11/04/1996 03:00:00
11/04/1996 04:00:00
11/04/1996 05:00:00
11/04/1996 06:00:00
11/04/1996 07:00:00
11/04/1996 08:00:00
11/04/1996 09:00:00
11/04/1996 10:00:00
11/04/1996 11:00:00
11/04/1996 12:00:00
11/04/1996 13:00:00
11/04/1996 14:00:00
11/04/1996 15:00:00
11/04/1996 16:00:00
11/04/1996 17:00:00
11/04/1996 18:00:00
11/04/1996 19:00:00
11/04/1996 20:00:00
11/04/1996 21:00:00
11/04/1996 22:00:00
11/04/1996 23:00:00
11/04/1996 00:00:00
12/04/1996 01:00:00
12/04/1996 02:00:00
12/04/1996 03:00:00
12/04/1996 04:00:00
12/04/1996 05:00:00
12/04/1996 06:00:00
12/04/1996 07:00:00
12/04/1996 08:00:00
12/04/1996 09:00:00
12/04/1996 10:00:00
12/04/1996 11:00:00
12/04/1996 12:00:00
12/04/1996 13:00:00
12/04/1996 14:00:00
12/04/1996 15:00:00
12/04/1996 16:00:00
12/04/1996 17:00:00
12/04/1996 18:00:00
12/04/1996 19:00:00
12/04/1996 20:00:00
12/04/1996 21:00:00
12/04/1996 22:00:00
12/04/1996 23:00:00
12/04/1996 00:00:00
13/04/1996 01:00:00
13/04/1996 02:00:00
13/04/1996 03:00:00
13/04/1996 04:00:00
13/04/1996 05:00:00
13/04/1996 06:00:00
13/04/1996 07:00:00
13/04/1996 08:00:00
13/04/1996 09:00:00
13/04/1996 10:00:00
13/04/1996 11:00:00
13/04/1996 12:00:00
13/04/1996 13:00:00
13/04/1996 14:00:00
13/04/1996 15:00:00
13/04/1996 16:00:00
13/04/1996 17:00:00
13/04/1996 18:00:00
13/04/1996 19:00:00
13/04/1996 20:00:00
13/04/1996 21:00:00
13/04/1996 22:00:00
13/04/1996 23:00:00
13/04/1996 00:00:00
14/04/1996 01:00:00
14/04/1996 02:00:00
14/04/1996 03:00:00
14/04/1996 04:00:00
14/04/1996 05:00:00
14/04/1996 06:00:00
14/04/1996 07:00:00
14/04/1996 08:00:00
14/04/1996 09:00:00
14/04/1996 10:00:00
14/04/1996 11:00:00
14/04/1996 12:00:00
14/04/1996 13:00:00
14/04/1996 14:00:00
14/04/1996 15:00:00
14/04/1996 16:00:00
14/04/1996 17:00:00
14/04/1996 18:00:00
14/04/1996 19:00:00
14/04/1996 20:00:00
14/04/1996 21:00:00
14/04/1996 22:00:00
14/04/1996 23:00:00
14/04/1996 00:00:00
15/04/1996 01:00:00
15/04/1996 02:00:00
15/04/1996 03:00:00
15/04/1996 04:00:00
15/04/1996 05:00:00
15/04/1996 06:00:00
15/04/1996 07:00:00
15/04/1996 08:00:00
15/04/1996 09:00:00
15/04/1996 10:00:00
15/04/1996 11:00:00
15/04/1996 12:00:00
15/04/1996 13:00:00
15/04/1996 14:00:00
15/04/1996 15:00:00
15/04/1996 16:00:00
15/04/1996 17:00:00
15/04/1996 18:00:00
15/04/1996 19:00:00
15/04/1996 20:00:00
15/04/1996 21:00:00
15/04/1996 22:00:00
15/04/1996 23:00:00
15/04/1996 00:00:00
16/04/1996 01:00:00
16/04/1996 02:00:00
16/04/1996 03:00:00
16/04/1996 04:00:00
16/04/1996 05:00:00
16/04/1996 06:00:00
16/04/1996 07:00:00
16/04/1996 08:00:00
16/04/1996 09:00:00
16/04/1996 10:00:00
16/04/1996 11:00:00
16/04/1996 12:00:00
16/04/1996 13:00:00
16/04/1996 14:00:00
16/04/1996 15:00:00
16/04/1996 16:00:00
16/04/1996 17:00:00
16/04/1996 18:00:00
16/04/1996 19:00:00
16/04/1996 20:00:00
16/04/1996 21:00:00
16/04/1996 22:00:00
16/04/1996 23:00:00
16/04/1996 00:00:00
17/04/1996 01:00:00
17/04/1996 02:00:00
17/04/1996 03:00:00
17/04/1996 04:00:00
17/04/1996 05:00:00
17/04/1996 06:00:00
17/04/1996 07:00:00
17/04/1996 08:00:00
17/04/1996 09:00:00
17/04/1996 10:00:00
17/04/1996 11:00:00
17/04/1996 12:00:00
17/04/1996 13:00:00
17/04/1996 14:00:00
17/04/1996 15:00:00
17/04/1996 16:00:00
17/04/1996 17:00:00
17/04/1996 18:00:00
17/04/1996 19:00:00
17/04/1996 20:00:00
17/04/1996 21:00:00
17/04/1996 22:00:00
17/04/1996 23:00:00
17/04/1996 00:00:00
18/04/1996 01:00:00
18/04/1996 02:00:00
18/04/1996 03:00:00
18/04/1996 04:00:00
18/04/1996 05:00:00
18/04/1996 06:00:00
18/04/1996 07:00:00
18/04/1996 08:00:00
18/04/1996 09:00:00
18/04/1996 10:00:00
18/04/1996 11:00:00
18/04/1996 12:00:00
18/04/1996 13:00:00
18/04/1996 14:00:00
18/04/1996 15:00:00
18/04/1996 16:00:00
18/04/1996 17:00:00
18/04/1996 18:00:00
18/04/1996 19:00:00
18/04/1996 20:00:00
18/04/1996 21:00:00
18/04/1996 22:00:00
18/04/1996 23:00:00
18/04/1996 00:00:00
19/04/1996 01:00:00
19/04/1996 02:00:00
19/04/1996 03:00:00
19/04/1996 04:00:00
19/04/1996 05:00:00
19/04/1996 06:00:00
19/04/1996 07:00:00
19/04/1996 08:00:00
19/04/1996 09:00:00
19/04/1996 10:00:00
19/04/1996 11:00:00
19/04/1996 12:00:00
19/04/1996 13:00:00
19/04/1996 14:00:00
19/04/1996 15:00:00
19/04/1996 16:00:00
19/04/1996 17:00:00
19/04/1996 18:00:00
19/04/1996 19:00:00
19/04/1996 20:00:00
19/04/1996 21:00:00
19/04/1996 22:00:00
19/04/1996 23:00:00
19/04/1996 00:00:00
20/04/1996 01:00:00
20/04/1996 02:00:00
20/04/1996 03:00:00
20/04/1996 04:00:00
20/04/1996 05:00:00
20/04/1996 06:00:00
20/04/1996 07:00:00
20/04/1996 08:00:00
20/04/1996 09:00:00
20/04/1996 10:00:00
20/04/1996 11:00:00
20/04/1996 12:00:00
20/04/1996 13:00:00
20/04/1996 14:00:00
20/04/1996 15:00:00
20/04/1996 16:00:00
20/04/1996 17:00:00
20/04/1996 18:00:00
20/04/1996 19:00:00
20/04/1996 20:00:00
20/04/1996 21:00:00
20/04/1996 22:00:00
20/04/1996 23:00:00
20/04/1996 00:00:00
21/04/1996 01:00:00
21/04/1996 02:00:00
21/04/1996 03:00:00
21/04/1996 04:00:00
21/04/1996 05:00:00
21/04/1996 06:00:00
21/04/1996 07:00:00
21/04/1996 08:00:00
21/04/1996 09:00:00
21/04/1996 10:00:00
21/04/1996 11:00:00
21/04/1996 12:00:00
21/04/1996 13:00:00
21/04/1996 14:00:00
21/04/1996 15:00:00
21/04/1996 16:00:00
21/04/1996 17:00:00
21/04/1996 18:00:00
21/04/1996 19:00:00
21/04/1996 20:00:00
21/04/1996 21:00:00
21/04/1996 22:00:00
21/04/1996 23:00:00
21/04/1996 00:00:00
22/04/1996 01:00:00
22/04/1996 02:00:00
22/04/1996 03:00:00
22/04/1996 04:00:00
22/04/1996 05:00:00
22/04/1996 06:00:00
22/04/1996 07:00:00
22/04/1996 08:00:00
22/04/1996 09:00:00
22/04/1996 10:00:00
22/04/1996 11:00:00
22/04/1996 12:00:00
22/04/1996 13:00:00
22/04/1996 14:00:00
22/04/1996 15:00:00
22/04/1996 16:00:00
22/04/1996 17:00:00
22/04/1996 18:00:00
22/04/1996 19:00:00
22/04/1996 20:00:00
22/04/1996 21:00:00
22/04/1996 22:00:00
22/04/1996 23:00:00
22/04/1996 00:00:00
23/04/1996 01:00:00
23/04/1996 02:00:00
23/04/1996 03:00:00
23/04/1996 04:00:00
23/04/1996 05:00:00
23/04/1996 06:00:00
23/04/1996 07:00:00
23/04/1996 08:00:00
23/04/1996 09:00:00
23/04/1996 10:00:00
23/04/1996 11:00:00
23/04/1996 12:00:00
23/04/1996 13:00:00
23/04/1996 14:00:00
23/04/1996 15:00:00
23/04/1996 16:00:00
23/04/1996 17:00:00
23/04/1996 18:00:00
23/04/1996 19:00:00
23/04/1996 20:00:00
23/04/1996 21:00:00
23/04/1996 22:00:00
23/04/1996 23:00:00
23/04/1996 00:00:00
24/04/1996 01:00:00
24/04/1996 02:00:00
24/04/1996 03:00:00
24/04/1996 04:00:00
24/04/1996 05:00:00
24/04/1996 06:00:00
24/04/1996 07:00:00
24/04/1996 08:00:00
24/04/1996 09:00:00
24/04/1996 10:00:00
24/04/1996 11:00:00
24/04/1996 12:00:00
24/04/1996 13:00:00
24/04/1996 14:00:00
24/04/1996 15:00:00
24/04/1996 16:00:00
24/04/1996 17:00:00
24/04/1996 18:00:00
24/04/1996 19:00:00
24/04/1996 20:00:00
24/04/1996 21:00:00
24/04/1996 22:00:00
24/04/1996 23:00:00
24/04/1996 00:00:00
25/04/1996 01:00:00
25/04/1996 02:00:00
25/04/1996 03:00:00
25/04/1996 04:00:00
25/04/1996 05:00:00
25/04/1996 06:00:00
25/04/1996 07:00:00
25/04/1996 08:00:00
25/04/1996 09:00:00
25/04/1996 10:00:00
25/04/1996 11:00:00
25/04/1996 12:00:00
25/04/1996 13:00:00
25/04/1996 14:00:00
25/04/1996 15:00:00
25/04/1996 16:00:00
25/04/1996 17:00:00
25/04/1996 18:00:00
25/04/1996 19:00:00
25/04/1996 20:00:00
25/04/1996 21:00:00
25/04/1996 22:00:00
25/04/1996 23:00:00
25/04/1996 00:00:00
26/04/1996 01:00:00
26/04/1996 02:00:00
26/04/1996 03:00:00
26/04/1996 04:00:00
26/04/1996 05:00:00
26/04/1996 06:00:00
26/04/1996 07:00:00
26/04/1996 08:00:00
26/04/1996 09:00:00
26/04/1996 10:00:00
26/04/1996 11:00:00
26/04/1996 12:00:00
26/04/1996 13:00:00
26/04/1996 14:00:00
26/04/1996 15:00:00
26/04/1996 16:00:00
26/04/1996 17:00:00
26/04/1996 18:00:00
26/04/1996 19:00:00
26/04/1996 20:00:00
26/04/1996 21:00:00
26/04/1996 22:00:00
26/04/1996 23:00:00
26/04/1996 00:00:00
27/04/1996 01:00:00
27/04/1996 02:00:00
27/04/1996 03:00:00
27/04/1996 04:00:00
27/04/1996 05:00:00
27/04/1996 06:00:00
27/04/1996 07:00:00
27/04/1996 08:00:00
27/04/1996 09:00:00
27/04/1996 10:00:00
27/04/1996 11:00:00
27/04/1996 12:00:00
27/04/1996 13:00:00
27/04/1996 14:00:00
27/04/1996 15:00:00
27/04/1996 16:00:00
27/04/1996 17:00:00
27/04/1996 18:00:00
27/04/1996 19:00:00
27/04/1996 20:00:00
27/04/1996 21:00:00
27/04/1996 22:00:00
27/04/1996 23:00:00
27/04/1996 00:00:00
28/04/1996 01:00:00
28/04/1996 02:00:00
28/04/1996 03:00:00
28/04/1996 04:00:00
28/04/1996 05:00:00
28/04/1996 06:00:00
28/04/1996 07:00:00
28/04/1996 08:00:00
28/04/1996 09:00:00
28/04/1996 10:00:00
28/04/1996 11:00:00
28/04/1996 12:00:00
28/04/1996 13:00:00
28/04/1996 14:00:00
28/04/1996 15:00:00
28/04/1996 16:00:00
28/04/1996 17:00:00
28/04/1996 18:00:00
28/04/1996 19:00:00
28/04/1996 20:00:00
28/04/1996 21:00:00
28/04/1996 22:00:00
28/04/1996 23:00:00
28/04/1996 00:00:00
29/04/1996 01:00:00
29/04/1996 02:00:00
29/04/1996 03:00:00
29/04/1996 04:00:00
29/04/1996 05:00:00
29/04/1996 06:00:00
29/04/1996 07:00:00
29/04/1996 08:00:00
29/04/1996 09:00:00
29/04/1996 10:00:00
29/04/1996 11:00:00
29/04/1996 12:00:00
29/04/1996 13:00:00
29/04/1996 14:00:00
29/04/1996 15:00:00
29/04/1996 16:00:00
29/04/1996 17:00:00
29/04/1996 18:00:00
29/04/1996 19:00:00
29/04/1996 20:00:00
29/04/1996 21:00:00
29/04/1996 22:00:00
29/04/1996 23:00:00
29/04/1996 00:00:00
30/04/1996 01:00:00
30/04/1996 02:00:00
30/04/1996 03:00:00
30/04/1996 04:00:00
30/04/1996 05:00:00
30/04/1996 06:00:00
30/04/1996 07:00:00
30/04/1996 08:00:00
30/04/1996 09:00:00
30/04/1996 10:00:00
30/04/1996 11:00:00
30/04/1996 12:00:00
30/04/1996 13:00:00
30/04/1996 14:00:00
30/04/1996 15:00:00
30/04/1996 16:00:00
30/04/1996 17:00:00
30/04/1996 18:00:00
30/04/1996 19:00:00
30/04/1996 20:00:00
30/04/1996 21:00:00
30/04/1996 22:00:00
30/04/1996 23:00:00
30/04/1996 00:00:00

Can you help me how to do this by using macro?

 

[suyah]

Dear Cyrilbrd,

Thanks!! I've tried this and it's work. Seconds were not increased.
However, I'm still facing problem with the date change to the next day when the time show 00:00:00.
I want it like this:

01/04/1996 23:00:00
01/04/1996 24:00:00
02/04/1996 01:00:00
...

and yes the time still show 00:00:00 rather than 24:00:00

p/s: actually I want it in macro because I want combine it with other code, so I just need click one button to do all that.

 

[suyah]

Dear WarPiglet,

=IFERROR(IF(MONTH($A$1)=MONTH(A1+0.04166666),A1+0.04166666,""),"")

I've also tried this. I don't know why I've got an error when I'm insert this formula. Fyi, I'm using Microsoft 2010.

p/s: Actually at A1, I want insert label "Date".

 

[suyah]

Thanks mfexel for reply!

Question: why the date is not 02/04/1996 00:00:00 after 01/04/1996 23:00??

The problem is I don't want it to be like that. I want the date still 01/04/1996 on 00:00:00.

 

[WarPigl3t]

Suyah,
If you want the date to still be the same at 00:00:00, then you will probably have to use a macro. But since the macro I provided you before is dependant on the value of the previous cell in the range, it will not work as you want. I have no idea how to go about overiding the protocol without affecting the increment formula.

 

[suyah]

WarPiglet,

Thank you for ur reply. I'm appreciate for your help all this time. I will try find the other way then

 

[cyrilbrd]

WarPiglet,

Thank you for ur reply. I'm appreciate for your help all this time. I will try find the other way then

If you want 24:00:00 in lieu of 00:00:00, I understand that this is for cosmetic purpose?
Will those time value be used for further computation or merely for visual representation. One alternative (not elegant) would be to summon an IF with TEXT and format all 1 into 24:00:00...