Date format conversion in Excel

0 votes

I have a list of dates, which seem to be in dd/mm/yyyy hh:mm format ( Ex - 12/13/2022 12:16 AM ) , which is incorrect. I need to change to mm/dd/yyyy hh:mm format.

I tried the formula 

=IF(H2="","",IF(DAY(H2)>12,DAY(H2)&"/"&MONTH(H2)&"/"&YEAR(H2),VALUE(DATE(YEAR(H2),DAY(H2),MONTH(H2)))))

But the result is #Value

Example

enter image description here

Jan 8 in Others by Kithuzzz
• 28,900 points
47 views

1 answer to this question.

0 votes

You appear to be misunderstanding the point: an Excel datetime format is a regular number, where 1 denotes a day beginning on January 1st, 1900.

I'll demonstrate how it appears on my PC.

enter image description here

As you can see, there are two ways to describe the thirteenth of December, 2022:

  • format dd/mm/yyyy : 13/12/2022 (European style)
  • format mm/dd/yyyy : 12/13/2022 (American style)
answered Jan 8 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,960 points
685 views
0 votes
1 answer

How to subtract date only format "mm/dd" in excel?

I suppose Excel has handled December 21 ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 53,520 points
56 views
0 votes
1 answer

Convert a date format in PHP

Use strtotime() and date(): $originalDate = "2010-03-21"; $newDate ...READ MORE

answered Feb 17, 2022 in Others by Aditya
• 7,660 points
1,906 views
0 votes
0 answers

Is there an efficient way to do this excel function

I wanted to divide a trip's departure ...READ MORE

2 days ago in Others by Kithuzzz
• 28,900 points
8 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
215 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
395 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
177 views
0 votes
1 answer
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 53,520 points
54 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP