date and time is not formatted in excel

[esaji]

I have a report and its date and time is not formatted and when i click filter it is not showing month day and time list from old date to new date

28-09-2021 11:56 AM
16-09-2021 03:25 PM
15-09-2021 01:36 PM
12-09-2021 10:42 AM
08-09-2021 04:55 PM


Can you please share any formula to make this date and time formatted in a new column ?

[alansidman]

Please Login or Register  to view this content.

[Sam Capricci]

copy an empty cell, then highlight the entire column and go to paste special, then select add then ok and it should change them to numeric and you can format as you want.

[Vraag en antwoord]

in B2: =--A2 will do. Make sure to set the cell format correctly. (dd-mm-yyyy hh:mm)

[esaji]

@Vraag en antwoord sorry it did not worked

Attachment 750067

[esaji]

Please Login or Register  to view this content.

Although it worked but the months are not correct it shall be september

Attachment 750070

[esaji]

copy an empty cell, then highlight the entire column and go to paste special, then select add then ok and it should change them to numeric and you can format as you want.

Can you create a animated gif to show me what you wanted to tell me ?

[Vraag en antwoord]

Your attachments cannot be opened. Quoting entire posts is unnecessary. Say what doesn't work instead of saying it doesn't work.

[esaji]

Thanks vraag

can you explain how this =--A2 formula is fixing the date issue i just wanted understand the logic and i can see that new column is not formatted like early you suggested ?dd-mm-yyyy hh:mm

[esaji]

Also I try to use the --A2 formula from the sheet you shared when i click the formula without changing anything it reproduce the same error

#VALUE!
on all column try to select the column B2 on formula bar and you will see this error

regards

[Vraag en antwoord]

#9 -- converts a number stored as text to a number.
#10 No idea. It just works here.

[GeoffW283]

First, be aware that your column-A "dates" are really just text that looks like a date.

None of the suggestions made so far work for me either. I think we're battling region code differences regarding what format of text Excel will automatically convert to a date.
With the --A1 approach 28-09-2021 11:56 AM gives me a #VALUE error because in the USA region Excel expects the 28 to be the month and the 09 to be the day. Similarly (and perhaps worse because there is no error) 12-09-2021 is interpreted as 9-Dec rather than 12-Sep.

There may be a more elegant way, but the following, I think, is region-proof:
In b3 copied down:

Please Login or Register  to view this content.

Don't forget to format col-B as a Date & Time type.

Note, for simplicity, this formula assumes leading zeros are always present (as your example does). That is: not
15-9-2021 1:36 PM, but always:
15-09-2021 01:36 PM

Hope this helps

[esaji]

GeoffW283

I really enjoyed your root cause analysis yes you are absolutelyright about region issue as I am in Saudi from last year although might be my excel date and time is set with (GMT+3) settings

your formula worked for me i tested and its correctly showing the date and time

many thanks

[alansidman]

Cannot see attachment. I believe the issue is one of US vs European Date Convention. Change the format to US convention and it should convert exactly as you wish.

[GeoffW283]

esaji - glad it works for you - thanks for the feedback