Excel unable to subtract dates in "dd/mm/yyyy hh:mm" format

1
votes

I have 2 adjacent columns in excel, each having dates in "dd/mm/yyyy hh:mm" format. But when I try to subtract them in the third column, it is unable to give me a numerical value, instead it shows the text "#value!" in the cell. How do I get the time difference between the 2 dates - enter image description here

1
regexexcelexcel-formulaformattingms-office
Then the "Dates" are text and not true Dates. You will need to convert them to true dates. My guess is that your local settings are mm/dd/yyyy not dd/mm/yyyy and as such Excel does not see them as dates. – Scott Craner
how can i convert all my cells (atleast 100) to true dates? maybe using a formula? – Dhruv Chadha
Use Text to Columns, it is on the data tab. – Scott Craner
ablebits.com/office-addins-blog/2015/03/26/… – Scott Craner
thanks man ! appreciate the help. – Dhruv Chadha

1 Answers

1
votes

Answered behalf of Scott Craner

Then the "Dates" are text and not true Dates. You will need to convert them to true dates. My guess is that your local settings are mm/dd/yyyy not dd/mm/yyyy and as such Excel does not see them as dates

Check below link to know more.

https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/