Formatting

[holla]

I need to format a date, but to actually change the value of the cell, rather than what it appears like. I would think there is something in excel to kind of drop the formatting and just leave the format as the value of the cell, but I had a post earlier which noone has responded to in a while, and noone knew how to do that. I am importing a date as 3/24/2003 and I want it to change to mm/dd/yy format, 03/24/03, but I want the actul cell to be that rather than what it looks like, it will be the actual value of the cell. I need this because I am saving the file as a .csv file, but then am sending it to someone else, so I can not control the settings on their computer. I would imagine that if there isnt a built in function in excel to do it, then a macro could do it.

Thanks

 

[nevillestoke]

How about using something like:

=MONTH(A3)&"/"&DAY(A3)&"/"&YEAR(A3)

where the date you want to convert is in cell A3.

You could then copy | pastespecial | values prior to exporting to .csv format

Neville

 

[Joe4]

Regarding dropping formatting:

Excel actually stores dates as numbers, specifically the number of days since January 1, 1900. To see this, highlight any date you have entered and change the formatting to General (which is the default).

This probably doesn't help you with your problem any, but just explains how Excel handles date values.

 

[nevillestoke]

Actually - a minor modification to the formula I posted earleir may be i order - this will place a "0" in front of the month (if less than 10) and truncate the year to two digits, rather than four.

=IF(MONTH(A3)<10,"0"&MONTH(A3)&"/"&DAY(A3)&"/"&RIGHT(YEAR(A3),2),MONTH(A3)&"/"&DAY(A3)&"/"&RIGHT(YEAR(A3),2))

I hope this helps

 

[holla]

Thank you very much, that and the paste special works for me.

 

[Mark W.]

Actually - a minor modification to the formula I posted earleir may be i order - this will place a "0" in front of the month (if less than 10) and truncate the year to two digits, rather than four.

=IF(MONTH(A3)<10,"0"&MONTH(A3)&"/"&DAY(A3)&"/"&RIGHT(YEAR(A3),2),MONTH(A3)&"/"&DAY(A3)&"/"&RIGHT(YEAR(A3),2))

I hope this helps

=TEXT(A3,"mm/dd/yy")

 

[nevillestoke]

Mark,

Sometimes the simplest answers elude us (or at least me) - I like your response much better, and it obviously accomplishes the same result.

The differences we exhibit are what makes the world go round - At least that's my theory and I'm sticking to it.

Cheers - Neville.