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, 2023 in Others by Kithuzzz
• 38,010 points
253 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, 2023 by narikkadan
• 63,420 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
• 23,260 points
1,288 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
• 63,420 points
418 views
0 votes
1 answer

Add a correct format date in Excel file with Python openpyxl

Use just datetime.fromisoformat(row['Date']) but I didn't have ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,420 points
2,032 views
0 votes
1 answer

Is there an efficient way to do this excel function

Use vlookup(): VLOOKUP(E2,$B$4:$C$8,2,1) READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,420 points
209 views
0 votes
1 answer

Excel - How do I round a date type to the next hour if it is more than one minute

Add almost 30 minutes and it'll get ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,420 points
196 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,670 points
1,237 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,670 points
1,425 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,420 points
2,746 views
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
• 63,420 points
838 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