Can\'t format the date properly Hello. I have the following set of datesbut I ca

Question

Can't format the date properly

Hello.

I have the following set of datesbut I can't figure out to properlly format them. Excel is formrating some dates in some manner and other dates in another. Atencioon. My regional settings (languagee) are not English.

The data is formatted as month/day/year hour:minutes and i would like the data to havee leading zeros

Let's look at some dates from that set:

6/21/2005 10:54

09/06/2005 09:13

2/24/1997 7:10

I would like them to be formatted like:

06/21/2005 10:54

09/06/2005 09:13

02/24/1997 07:10

I've pasted that set of dates in excel and tried to give the personalized format like mm/dd/aaaa hh:mm

The first data is formatted like:

06/21/2005 10:54

and the second as this behaviour:

If using the formula to check if it's a number, first date returns FALSE and second TRUE

I can't seem to understand this behaviour.

Can someone explain this to me?

Thanks,

Can't format the date properly Hello. I have the following set of datesbut I can't figure out to properlly format them. Excel is formrating some dates in some manner and other dates in another. Atencioon. My regional settings (languagee) are not English. The data is formatted as month/day/year hour:minutes and i would like the data to havee leading zeros Let's look at some dates from that set: 6/21/2005 10:54 09/06/2005 09:13 2/24/1997 7:10 I would like them to be formatted like: 06/21/2005 10:54 09/06/2005 09:13 02/24/1997 07:10 I've pasted that set of dates in excel and tried to give the personalized format like mm/dd/aaaa hh:mm The first data is formatted like: 06/21/2005 10:54 and the second as this behaviour: Can't format the date properly Hello. I have the following set of datesbut I can't figure out to properlly format them. Excel is formrating some dates in some manner and other dates in another. Atencioon. My regional settings (languagee) are not English. The data is formatted as month/day/year hour:minutes and i would like the data to havee leading zeros Let's look at some dates from that set: 6/21/2005 10:54 09/06/2005 09:13 2/24/1997 7:10 I would like them to be formatted like: 06/21/2005 10:54 09/06/2005 09:13 02/24/1997 07:10 I've pasted that set of dates in excel and tried to give the personalized format like mm/dd/aaaa hh:mm The first data is formatted like: 06/21/2005 10:54 and the second as this behaviour: If using the formula to check if it's a number, first date returns FALSE and second TRUE I can't seem to understand this behaviour. Can someone explain this to me? Thanks,

Explanation / Answer

Hi,

The reason is pretty obvious. Not sure where your data come from but they're not all Dates from an Excel perspective and this is confirmed bby is the ISNUMBER function you used to test them.

You should reformat those non-dates as real dates to dissplay them aas you expect. Assuming your dates are in A1 through A100 enter the following formula in B1 and copy it down until B100:

=IF(ISNUMBER(A1),A1,DATE(MID(A1,6,4),LEFT(A1,1),MID(A1,3,2))+TIME(MID(A1,11,2),MID(A1,14,2),0))

Hope this helpss!!

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.