Difference between dates in different date formats

Khaled

New member
Joined
Mar 23, 2016
Messages
3
Reaction score
0
Points
0
Hi,
I know that calculating the difference between two dates is doable in excel using the subtraction or "DATEDIF" function if the date format is month/day/year.
However, is it possible to calculate the difference between dates if the format is day/month/year? I tried it, but the result is (#Value!) even if I changed the format of the date cells to be in the format of day/month/year.
Any Solution for this?
I am using Excel 2013
 
Sounds like the info in the cell is probably not actually a date. Look into various Date functions to convert to a proper date. A date is actually a number ie 3/23/2016 is really 42452. If you format the cell with the date to General you will see this, it does not, then it is not an actual date.

If you post an example file would be easier to show how to sort this out. There are a variety of approaches possible depending on the actual data.

Cheers
 
Thanks Jafa.
You can see the file attached. The format is custom:a date in the format of day/month/year
 

Attachments

  • Date Test.xlsx
    12.4 KB · Views: 20
In your example, the date in B3 is a text string. You cannot see it easily because you right aligned the cell.
If you remove this alignment, the string will align left in the cell indicating it is text.

So, B3-B2 will not work because you cannot subtract number and text.
There are various ways to solve the problem.
One of them is to select the "text date" cell - Data ribbon - Click Text to columns, and immediately click Finish.

Remember that to excel a date is an integer representing number of days elapsed since 01/01/1900 ( if not using the 1904 system). The formatting is how XL shows you a date but does NOT affect the underlying value
 
Agree with what Pecoflyer says re formatting and dates really being numbers.

That said, if you copy the formula in A5 to B5, it seems that Excel is able to understand the text as a date and calculate the difference. I was a bit surprised at this.

Have played with the file and shown how you might convert the text to a date using formulas, in case you have a large data set to work with. Decided to separate the components to make it easier to follow, but you could combine into one large formula, would be difficult to read though. There are probably user defined functions (VBA code) on the web that do this too.
 

Attachments

  • ExcelGuru Date Test.xlsx
    13 KB · Views: 20
Thanks Pecoflyer,
I tried the way you mentioned to change the text date cell, however, the result is still the same: "#Value!".
I changed the cell type into "Date", of short date type, but it did not work also, unfortunately.
 
Back
Top