#Value! result for date subtraction formula

[happen]

Hello,
I am trying to subtract two dates using DatedIf, ie., DatedIf(C5-C6,"D", but the two dates I am subtracting are the same dates (08/03/2023 - 08/03/2023) and should equal zero but I am getting a #Value! error. I've tried changing the date formatting to several different formats and even tried general, text and custom and still get the error. I am using O365 Excel. Thank you!

 

[Joe4]

Welcome to the Board!

You either subtract the two dates, or use DATEDIF, but not both together, i.e.
use:

=C5-C6

or

=DATEDIF(C5,C6,"D")

Note that simple subtraction will work here because Excel stores dates as the number of DAYS since 1/0/1900.
So if you subtract any two dates, the number returned will always represent number of DAYS, by default.

 

[Fluff]

You shouldn't be subtracting them, use

=DATEDIF(C5,C6,"d")

or simply

=c5-c6

 

[happen]

Welcome to the Board!

You either subtract the two dates, or use DATEDIF, but not both together, i.e.
use:

=C5-C6

or

=DATEDIF(C5,C6,"D")

Note that simple subtraction will work here because Excel stores dates as the number of DAYS since 1/0/1900.
So if you subtract any two dates, the number returned will always represent number of DAYS, by default.

Correct, I tried using either one and no matter what I am getting the #Value!. I was hoping to find out why I am not getting a 0 as a result.

 

[happen]

You shouldn't be subtracting them, use

=DATEDIF(C5,C6,"d")

or simply

=c5-c6

Correct, I tried using either one and no matter what I am getting the #Value!. I was hoping to find out why I am not getting a 0 as a result.

 

[Fluff]

That sounds as if your £dates" are really text & not proper dates.
If you use

=isnumber(c5)

and the same for c6 what do they both return?

 

[happen]

That sounds as if your £dates" are really text & not proper dates.
If you use

=isnumber(c5)

and the same for c6 what do they both return?

The result was true

 

[Joe4]

The result was true

For both?
Please sure us your data and exact formula you are trying.

 

[happen]

For both?
Please sure us your data and exact formula you are trying.

That worked. Thank you! Why didn't the format change when I highlighted all the cells and applied the date fomat? Anyway, Thank you!

 

[Joe4]

That worked. Thank you! Why didn't the format change when I highlighted all the cells and applied the date fomat? Anyway, Thank you!

If you change the format of a cell and the display does not change, that usually indicates that you are dealing with a text value.
Note that changing the format of the cell does not actually change the value of the cell. You have to re-enter the value.