Customise a day format

[Kubes1]

Hi,

I need help redefining how excel reads a day. For example, our processes use 06:00:01 AM today to 06:00:00 AM next day to define today.

I want to create a formula that will return the correct day in the format dd/mm/yyyy (with no time) based on the above scenario. Example:

say cell A2 has 02/05/2020 05:05:00 AM, then cell B2 formula should return 01/05/2020.

I've been doing this manual but it taking me lots of time.

See below. The formula needs to be in column B using data from column A. I completed Column B manually by looking at values in Column. Will appreciate help.

Trans Date (Column A) Date (Column B)
02/01/2017 11:05:01 AM 1/2/2017
03/01/2017 1:53:27 AM 1/2/2017
03/01/2017 1:53:27 AM 1/2/2017
03/01/2017 4:05:36 PM 1/3/2017
03/01/2017 8:34:19 PM 1/3/2017
03/01/2017 9:34:03 PM 1/3/2017
03/01/2017 11:27:15 PM 1/3/2017
04/01/2017 5:21:05 AM 1/3/2017
05/01/2017 10:44:53 AM 1/5/2017
07/01/2017 5:20:57 AM 1/6/2017
07/01/2017 5:23:06 AM 1/6/2017
07/01/2017 5:26:08 AM 1/6/2017
08/01/2017 7:38:06 PM 1/8/2017
08/01/2017 7:43:32 PM 1/8/2017
09/01/2017 1:56:39 AM 1/8/2017
09/01/2017 1:59:05 AM 1/8/2017
09/01/2017 2:29:40 AM 1/8/2017
09/01/2017 2:53:32 AM 1/8/2017
09/01/2017 2:29:08 PM 1/9/2017
09/01/2017 6:38:04 PM 1/9/2017
09/01/2017 8:24:43 PM 1/9/2017
14/01/2017 9:03:48 AM 1/14/2017
14/01/2017 12:54:21 PM 1/14/2017
14/01/2017 12:54:29 PM 1/14/2017
14/01/2017 4:50:41 PM 1/14/2017
15/01/2017 11:28:55 AM 1/15/2017
15/01/2017 9:39:19 PM 1/15/2017
15/01/2017 9:43:01 PM 1/15/2017
15/01/2017 10:25:18 PM 1/15/2017
16/01/2017 3:16:23 AM 1/15/2017
16/01/2017 3:39:59 AM 1/15/2017
16/01/2017 3:53:06 AM 1/15/2017
16/01/2017 4:14:37 AM 1/15/2017
16/01/2017 8:47:56 PM 1/16/2017
16/01/2017 9:07:18 PM 1/16/2017
18/01/2017 4:26:32 PM 1/18/2017
19/01/2017 3:04:03 AM 1/18/2017
20/01/2017 6:17:42 PM 1/20/2017
20/01/2017 11:29:22 PM 1/20/2017
21/01/2017 12:41:27 AM 1/20/2017
21/01/2017 1:59:51 PM 1/21/2017
21/01/2017 2:05:48 PM 1/21/2017
22/01/2017 1:47:00 PM 1/22/2017
22/01/2017 1:47:13 PM 1/22/2017
22/01/2017 4:47:27 PM 1/22/2017
22/01/2017 4:47:46 PM 1/22/2017
23/01/2017 7:41:32 AM 1/23/2017
23/01/2017 7:50:17 PM 1/23/2017
23/01/2017 9:13:55 PM 1/23/2017
23/01/2017 11:56:57 PM 1/23/2017
24/01/2017 1:41:24 AM 1/23/2017
24/01/2017 1:48:39 AM 1/23/2017
24/01/2017 9:41:42 AM 1/24/2017
24/01/2017 8:54:35 PM 1/24/2017
24/01/2017 9:55:26 PM 1/24/2017
25/01/2017 12:49:43 AM 1/24/2017
25/01/2017 1:03:04 AM 1/24/2017
27/01/2017 12:27:06 AM 1/26/2017
27/01/2017 3:48:11 AM 1/26/2017
28/01/2017 4:53:38 AM 1/27/2017
31/01/2017 11:29:21 AM 1/31/2017
31/01/2017 5:32:00 PM 1/31/2017

[bebo021999]

How about:

=INT(A1-6/24)

[Kubes1]

Now, that works! Thanks a lot bebo021999.

Much appreciated. I love this forum.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.