Date conversions

[norfolk62]

I have an excel file that has dates in the european format of dd/mm/yy hh:mm:ss but excel does not allow me to perform calculations on the cells. I receive the #value error. I want to subtract start date from finish date to derive days outstanding. Is there an easy fix? Any guidance will be appreciated.

 

[Desu Nota from Columbus]

I have an excel file that has dates in the european format of dd/mm/yy hh:mm:ss but excel does not allow me to perform calculations on the cells. I receive the #value error. I want to subtract start date from finish date to derive days outstanding. Is there an easy fix? Any guidance will be appreciated.

I just created a sample worksheet and it worked fine. Click on one of your dates and select the "General" format and look at the sample window. Does it show a number like 40668?

I entered the date 5-5-11 and then custom formatted it to dd/mm/yy hh:mm:ss and then subtracted it from 25/05/11 12:00:00 and it worked. The recipient cell I changed the format to "dd" and it showed 20.

 

[jasonb75]

Parse the dates with text to columns to convert them to U.S. format.

Select the column with the dates (if you have 2 columns do 1 at a time).

Navigate your excel menu Data > Text to columns > Delimited

UnCheck all boxes, click next, then set the date format in the dropdown to MDY and hit Finish.

Repeat for any other columns.