Convert Wrong Date & Time format to Correct Date & Time Format

[Morshed_Dhaka]

Hey Everyone,

seeking a small help to Convert Wrong Date & Time format to Correct Date & Time Format.

I have good amount of transaction data from system where it shows the date & time as per below way :

19 Dec 2020 01:21:03 AM

19 Dec 2020 01:37:20 AM

19 Dec 2020 01:58:46 AM

19 Dec 2020 04:35:55 AM

19 Dec 2020 04:52:58 AM

19 Dec 2020 05:02:49 AM

19 Dec 2020 05:06:59 AM

19 Dec 2020 11:31:22 AM

19 Dec 2020 11:46:11 AM

19 Dec 2020 12:36:48 PM

the red coloured highlighted data are in wrong format. i need a formula next to this column to convert all the wrong format into right date & time format & keep the correct format as it is.

Advacne thanks for that person who will give me a quick support.

Thanks.

 

[maabadi]

What is Problem with red colored data????

 

[Morshed_Dhaka]

What is Problem with red colored data????

it is showing 01:21:03 AM which is actually not. a financial transaction can't be possible at 01:21 AM. actually it should be 01:23 PM. check the last data. 12:36 is capturing correctly & showing PM. but when it crosses the time 12:59:59 PM then it is showing AM (red coloured data)

 

[maabadi]

I don't Know what is your minimum time. For Example if your minimum time acceptable is 10:00:00 AM then use this formula for first cell (if is A2): at B2:

=IF(MOD(A2,1)<(10/24),A2+0.5,A2)

 

[Morshed_Dhaka]

I don't Know what is your minimum time. For Example if your minimum time acceptable is 10:00:00 AM then use this formula for first cell (if is A2): at B2:

=IF(MOD(A2,1)<(10/24),A2+0.5,A2)

Hi maabadi, i have no minimum time. i just want to reflect the actual time in the excel report.

For example : 2 invoice created in the system. Invoice A & B on 19 Dec 2020.

Invoice A created on 19 Dec 2020 at 12:36:48 PM > in the excel CSV report, it is showing "19/12/2020 12:36:48 PM"
Invoice B created on 19 Dec 2020 at 04:52:58 PM > in the excel CSV report it is showing " 19/12/2020 04:52:58 AM"

i hope now you will get my point. when i doing filtering in the csv report , it is showing that some invoice created 19/12/2020 04:52:58 AM which is not possible as becuase that is mid-night. it supposed to be 04:52:58 PM 16:52:58 PM

 

[maabadi]

The way I know is do it based criteria. if you don't have it you should consider one minimum or maximum for you or find it on your data.

 

[pedro-egoli]

Possible problem is a 12 hour clock and 24hour clock conflicting.
For what its worth I input your dates using number and time formats and this shows

Book1.csv
	H	I
1	number format	time format
2	44184.53	12:36:48 PM
3	44184.20	4:52:58 AM
4	0.32	7.73
Book1

Cell Formulas
Range	Formula
H4	H4	=SUM(H2-H3)
I4	I4	=SUM(I2-I3)*24

Make that as you will but it may give you food for thought