#value! error on date calculations

[whitepaw]

Hi, I am trying to add / subtract one date from another in Excel 2016 (and do other date related calculations) and keep getting the #value! error.
Note that I live in New Zealand so want to use the format Date, English (New Zealand) (dd/mm/yyyy). I can use this formula without problems in older versions of Excel but can’t understand why this is a problem in this version. Please help. Thanks in advance

[Pete_UK]

Those "dates" are actually text values that just look like dates.

I got a result by clicking on the formula bar while A3 was selected (as if to edit it), then pressing < Enter >.

Hope this helps.

Pete

[whitepaw]

Hi Pete, thanks for your response but I don't understand your answer. I would rather understand how to show these dates as dates and not text values. The only way I could make the formula work was to write the dates as mm/dd/yyyy (the American way) instead of dd/mm/yyyy (the NZ way), but then isn't that what the format cell function is for. As I mentioned in original post, I can do this no problem on older versions of excel, don't understand why this doesn't work on my 2016 version. Can you show me how to write these dates as dates, and not text? Many thanks

[Sam Capricci]

usually you can set up the way excel recognizes dates under the options section of excel. Go to the file tab then to options.

[scottylad2]

Hi Pete, thanks for your response but I don't understand your answer. I would rather understand how to show these dates as dates and not text values. The only way I could make the formula work was to write the dates as mm/dd/yyyy (the American way) instead of dd/mm/yyyy (the NZ way), but then isn't that what the format cell function is for. As I mentioned in original post, I can do this no problem on older versions of excel, don't understand why this doesn't work on my 2016 version. Can you show me how to write these dates as dates, and not text? Many thanks

check the preferences within the programme to make sure you're regional settings are correct

[popipipo]

Put a 1 in a empty cel
Copy this cell
Select column A
then 'paste special' 'multiply'
The text should now be a number.
Change then cell format to (dd/mm/yyyy)
Remove the 1