How do I convert the date format into a different style?

For any one of your dates, have a cell point to it (i.e. = a2 if a date of yours is in cell a2). When you do that, does the value come up as what your date looks like, or is it a number like 40,000?

Crystal

It sounds like these dates aren't really 'dates', perhaps they are text which appear to be dates.

PS Why would you need the date in that particular format for a calculation?

The format of a value shouldn't actually matter - it's the actual value that's important.

The format of a date should not affect the behaviour of a calculation. What calculation do you wish to perform?

If you have a date in A1 what does

=ISNUMBER(A1)

return. If it returns FALSE you have text dates.

Changing the format of a date only changes its appearance. Internally it is still a date, ie number. So changing for "calculation purposes" is irrelevant as you're not changing the value in the cell at all.

Perhaps these dates are text?

Hey Guys -

I am currently using it for Attrition calculations:
For example:

Plugging in the Start Date and then the End Date - is automatically calculating a formula for the length of time and for the tenure that an employee has been employed.

<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=464><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=17 width=64>Start Date</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=81>End Date</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 147pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=196>Length of Time</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=123>Tenure</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>6-Jun-07</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>15-May-08</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0 years 11 months 9 days</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>6 months - 1 year</TD></TR></TBODY></TABLE>

The Length of time Formula:

(=DATEDIF(D17, V17, "y")&" years "&DATEDIF(D17,V17,"ym")&" months "&DATEDIF(D17,V17,"md")&" days"

AND

the Tenure formula:
=LOOKUP(DATEDIF(D28,V28,"m"),{0,3,6,12,24,60},{"0-3 months","3-6 months","6 months - 1 year","1-2 years","2-5 years","5 years +"})

Both require the start dates and end dates to read as DATES. For some reason - the data I have received is showing Start Dates and End Dates but it is not allowing me to change it into the "STYLE" that I need it to read. The result is that I am not able to plug these 700+ start and end dates to be able to get the calculations for Tenure and Length - (Which is needed for pie charts)

Thus - I notice that when I highlight the entire row -of Start Dates - and right click to change to the proper date format - it is for some reason NOT changing ALL the dates into a consist format. And it seems to continue to go right back into a format that I HAVE NOT selected on my own...
Custom: [$-409]d-mmm-yy;@ ... and I have NO idea why it keeps going there.

HELP !!!!!

Special KK - I think your right - some of the dates are Texts not Dates. which is why it is not reading it.

How can I c hange a Text date into a Date format that excek will recognize?

Crystal, I rarely have this issue, but i think you can research the "Text to Columns" function in the Excel help files or this message board.

I forget how it works but basically it takes text values (which some of your dates are) and then puts them into columns and makes them not defined as text any more.

Neither of the formulas you posted return date values - they return text, which I don't think will be useful in any calculation(s).

I don't really have an idea what you mean by 'Attrition' or 'Tenure' in this context, or what you are trying to do.

Crystal,

Yours is a peculiar problem non US user always have when they get data (with dates) from US systems.

US Date format is Month Day Year (MDY), the rest of us (you included I presume) use Day Month Year (DMY). It seems to me that your dates are in MDY format and will be recognised as text on your computer. To convert all these text dates to real dates (numbers) carry out the following steps.

1. Highlight all the dates: (if they are in column A you could just highlight the entire column to save time)
2. Go to Data - Text to column (Alt+A+E) in Excel 2007, or Tools>Text to Column in 2003
3. Click Next
4. Click Next Again
5. Click on the Date radio button and choose MDY from the drop down menu
6. Click Finish

All your dates will now be converted to real dates and your calculations will work. I advice that you always use this custom date format to ensure you know your dates are dates: go to format cells and the custom date tab and type this under the Type: box

ddd dd mmm yy

6/4/2010 will read Tue 06 Apr 10
14/4/2010 will read Mon 14 Apr 10

Note: If your system is set up as United States English 6/4/2010 will read as Fri 04 Jun 2010 and 14/4/2010 will not changes since there isn't a 14th month of the year. The United States uses MDY format.

This seemingly simple solution to your problem is the cause of most people anguish in performing data analysis of any sort. They get theirs daa from a US Parent Company with all the dates in MDY format and some have to manually change all the dates to DMY format before analysis, PIVOT Tables won't work either.

hope this helps