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

Morshed_Dhaka

New Member
Joined
Dec 16, 2016
Messages
42
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What is Problem with red colored data????
 

Morshed_Dhaka

New Member
Joined
Dec 16, 2016
Messages
42
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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:
Excel Formula:
=IF(MOD(A2,1)<(10/24),A2+0.5,A2)
 

Morshed_Dhaka

New Member
Joined
Dec 16, 2016
Messages
42

ADVERTISEMENT

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:
Excel Formula:
=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

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
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
HI
1number formattime format
244184.5312:36:48 PM
344184.204:52:58 AM
40.327.73
Book1
Cell Formulas
RangeFormula
H4H4=SUM(H2-H3)
I4I4=SUM(I2-I3)*24

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

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,042
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top