Ask Your Question
0

Calc amount of days between dates [solved]

asked 2020-02-29 23:21:16 +0100

Tyco72 gravatar image

updated 2020-03-02 03:10:18 +0100

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2020-03-01 03:08:32 +0100

Inclement gravatar image

updated 2020-03-01 03:09:17 +0100

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.

edit flag offensive delete link more

Comments

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.

Tyco72 gravatar imageTyco72 ( 2020-03-01 11:30:32 +0100 )edit

It is best to use the DATEDIF function

Schiavinatto gravatar imageSchiavinatto ( 2020-03-01 14:23:34 +0100 )edit
1

DATEDIF() is unnecessary.

erAck gravatar imageerAck ( 2020-03-01 20:11:15 +0100 )edit

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

erAck gravatar imageerAck ( 2020-03-01 20:12:02 +0100 )edit

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

Inclement gravatar imageInclement ( 2020-03-02 00:02:30 +0100 )edit
1

In Portuguese DATADIF, in English DATEDIF

image description

"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.

Schiavinatto gravatar imageSchiavinatto ( 2020-03-02 00:36:16 +0100 )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?

Inclement gravatar imageInclement ( 2020-03-02 01:25:29 +0100 )edit

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

Inclement gravatar imageInclement ( 2020-03-02 01:34:15 +0100 )edit
1
Schiavinatto gravatar imageSchiavinatto ( 2020-03-02 03:09:23 +0100 )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.

Inclement gravatar imageInclement ( 2020-03-02 06:00:29 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-02-29 23:21:16 +0100

Seen: 5,786 times

Last updated: Mar 01 '20