CODEWITHSUNDEEP
excel
TheRealPapa
TheRealPapa

Reputation: 4539

Excel difference between dates not working

I have the following data in columns:

            A            B
1       1/08/2021    29/11/2021

These values are the result of some searches from other columns with this formula:

=MINIFS('sheet2'!$G:$G,'sheet2'!$F:$F,$E1)

Both the source cells and the resulting cells are of type date

I then look to get the DATEDIF between these and I end up with #NAME?

=DATEDIF($A1,$B1,”M”) /* Corrected the above "W" to "M"... typo! */

enter image description here

I tried DATEVALUE on the search result cells like this:

=DATEDIF(DATEVALUE($A1),DATEVALUE($B1),”M”)

But I end up with #VALUE? so the resulting values from the searches are not seen as dates. What have I missed? Thanks!

Upvotes: 1

Views: 54

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You have two mistakes:

The #NAME? error is because you are using left and right quotation marks instead of straight quotation marks. eg: “W” instead of "W".

If you correct that, you will see a #NUM! error because W is not a valid argument for the function.

See MS Help for the DATEDIF function for more information as well as a warning about the MD unit.

Upvotes: 0

Fernando Barbosa
Fernando Barbosa

Reputation: 1134

The "W" on =DATEDIF($A1,$B1,”W”) is not a valid unit, accordingly to Microsoft's documentation (here).

The list of valid units is:

  • "Y" : The number of complete years in the period.
  • "M" : The number of complete months in the period.
  • "D" : The number of days in the period.
  • "MD": The difference between the days in start_date and end_date. The months and years of the dates are ignored. Not recommended, as there are known limitations with it.
  • "YM" : The difference between the months in start_date and end_date. The days and years of the dates are ignored
  • "YD" : The difference between the days of start_date and end_date. The years of the dates are ignored.

Upvotes: 1

Related Questions