We will be migrating from Ask to Discourse on the first week of August, read the details here

# Calc amount of days between dates [solved] [closed]

Hello. I have a simple problem but the answers which I have found, don't work. I have to calculate the number of days between two dates. Example:

A1=27-01-2010

A2=02-11-2011

C2=A2-A1 I can't let it work.

A1 and A2 are correctly formatted as date DD-MM-YYYY

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2021-05-27 13:28:05.477759

Sort by » oldest newest most voted

What just worked for me was to format the cells before entering the dates. When I typed in the dates first, and then applied the formats over top of them, I got #VALUE! from the formula. Format then data entry gave me 644.

more

1

Thank you, it seems to work! Another detail is that the destination cell, where the amount of days is calculated (C2 here), has to be formatted as number and not as date.

( 2020-03-01 11:30:32 +0200 )edit

It is best to use the DATEDIF function

( 2020-03-01 14:23:34 +0200 )edit
1

DATEDIF() is unnecessary.

( 2020-03-01 20:11:15 +0200 )edit

#VALUE! happens if one of the operands is not a numeric date but text content.

( 2020-03-01 20:12:02 +0200 )edit

DATEDIF() gives me Err:511 for the same dates that subtraction works for. Dropbox link

( 2020-03-02 00:02:30 +0200 )edit
1

In Portuguese DATADIF, in English DATEDIF

"d" = Number of whole days between Start date and End date.

"m" = Number of whole months between Start date and End date.

"y" = Number of whole years between Start date and End date.

"ym" = Number of whole months when subtracting years from the difference of Start date and End date.

"md" = Number of whole days when subtracting years and months from the difference of Start date and End date.

"yd" = Number of whole days when subtracting years from the difference of Start date and End date.

( 2020-03-02 00:36:16 +0200 )edit

I must be doing something else wrong, then, because I still get errors. Thanks for the interval information; I just found it in Help, too.

Actually, Help examples only show DATEDIF() used in formulas with the dates also in the formulas, not in other cells. I wonder if that's the issue. Can you only have DATEDIF("2020-03-01";"2020-02-01";"d") and not DATEDIF(A2;A1;"d") where A2 and A1 contain dates?

( 2020-03-02 01:25:29 +0200 )edit

Maybe someone would kindly add a "datedif" tag to this thread? :)

( 2020-03-02 01:34:15 +0200 )edit
1
( 2020-03-02 03:09:23 +0200 )edit

Oh ack. I had the end date cell reference first like a dummy. Thank you, @Schiavinatto.

By the way, what I said up above is wrong, yes you can use cell references. It's too late to edit that out now.

( 2020-03-02 06:00:29 +0200 )edit