Excel-Convert date to general format

[ashz]

Hi,

How can I automate a conversion of date to general?

Thx

 

[zelgar]

Right click on cell, Format Cells, Number tab, General

 

[SkipVought]

Hi,

What is it that you are trying to accomplish by changing the date formatting to general?

Also what version of Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ashz]

Hi,

I wish to take the Date from another cell and have it in general in another cell.

I need it to calculate days, any other idea?

the version is excel 2007.

Thx

 

[SkipVought]

If you have REAL DATES, you do not need to change the format in order to do data calculations. Duration is simply the arithmatic difference between two dates.

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

It all depends on how your data was entered in the spreadsheet. If the data was entered in a mm/dd/yyyy format (e.g., 06/16/2011); you can easily find the difference in dates by subtracting the cells (e.g., =b1-a1). There are several date formats, so the easiest way to see if your date is really a number is by right clicking the cell containg the date and click Format Cell, Number tab and click General. If the date changes to a weird number (e.g., 06/16/2011 changes to 40710) then you can easily calculate the days.

If for example your data is set up like this: Col A has month, Col B has day, col C has year; use the DATEVALUE function like this: =DATEVALUE(CONCATENATE(A20,"/",B20,"/",C20)). Unless the cell has already been formatted for a Number format - Date, it will probably give you a result like 40710 if the value was for 06/16/2011.

Once the data is in a numeric format, you can simply subtract the two fields to get the time in days.

 

[SkipVought]

It all depends on how your data was entered in the spreadsheet.

This is not true. A Date is a Date, regardless of how it is entered.

The caveat is that Bill Gates lives in the state of Washington, USA so any entry that leads with day, will be assumed to be month and invalid strings will not be converted.

Date format has nothing to do with date arithmetic or collation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

This is not true. A Date is a Date, regardless of how it is entered.

Using your owns words is the best argument...

If you have REAL DATES

Depending on how the dates were entered in the spreadsheet is important. As you stated, if the data are REAL DATES which means that the data was inputted in a format that Excel recognizes as a date(number) and has automatically converted the number into a Date, Number format; it is simple to calculate the number of days between the dates.

If, however, the dates are not in a format that Excel recognizes (e.g., the user inputed them in dd/mm/yyyy) or the data is split into multiple columns (e.g., Col A is Month, Col B is day, Col C is year); then additional work is required to convert the date into a date (e.g., using the DATEVALUE function).

 

[SkipVought]

zelgar,

If you would have taken the time to read the posted faq, then it should have been somewhat obvious that your treatise was, in great measure, redundant and somewhat misleading.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

I did read the FAQ, but it's not really relavent to the question. The FAQ indicated that dates/times are different from DURATIONS, but didn't elaborate. The fact that by subtracting two dates from each other DOES give you the DURATION.

Provided that the data is in a date format that Excel recognizes (e.g., dd/mm/yyyy) and converts to a numeric value; it's very simple to do what the OP asked.

If, however, the dates are not in this format, additional work is needed to first convert the data from the spreadsheet into a date format(e.g., using the DATE function). As you're aware of the other thread dealing with the user who inputted his dates in a dd/mm/yyyy format which resulted in some problems.

 

[SkipVought]

I guess that you did not follow the link to Microsoft.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}