CODEWITHSUNDEEP
excelgoogle-sheets
Skorpion
Skorpion

Reputation: 63

How to calculate the difference between two (2) dates

im looking for calcultion of two dates (Dates - Today) where i get Year,months and Days left from today

there is some expired dates as well which i need - year - months and - days

here is my dates are

02/11/2022
31/08/2020
21/01/2021
02/11/2022
02/11/2022
05/04/2023
28/02/2021
06/02/2021
06/02/2021
21/12/2020
11/02/2021
10/02/2021
01/08/2023
11/07/2023
11/09/2019
11/04/2023
13/05/2020
08/01/2023
08/01/2021
08/01/2021

Upvotes: 0

Views: 564

Answers (2)

General Grievance
General Grievance

Reputation: 4988

Here's an ArrayFormula translation of Gary's Student's answer for Google Sheets, if you're using that:

=ArrayFormula(
    FILTER(
        IF(
            TO_DATE(A:A)<TODAY(),
            "expired",
            DATEDIF(TODAY(),A:A,"y")&" years, "&
                DATEDIF(TODAY(),A:A,"ym")&" months, "&
                DATEDIF(TODAY(),A:A,"md")&" days"
        ),
        LEN(A:A)
    )
)

The TO_DATE forces a type change to a date so the comparison will work even if the column is a number or text value.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Answer implemented in Excel rather than Google-sheets

with data in column A, in B1 enter:

=IF(A1<TODAY(),"expired",DATEDIF(TODAY(),A1,"y")&" years, "&DATEDIF(TODAY(),A1,"ym")&" months, "&DATEDIF(TODAY(),A1,"md")&" days")

and copy downwards:

enter image description here

If you required the year month day for the expired dates, use:

=IF(A1<TODAY()," expired " & DATEDIF(A1,TODAY(),"y")&" years, "&DATEDIF(A1,TODAY(),"ym")&" months, "&DATEDIF(A1,TODAY(),"md")&" days",DATEDIF(TODAY(),A1,"y")&" years, "&DATEDIF(TODAY(),A1,"ym")&" months, "&DATEDIF(TODAY(),A1,"md")&" days")

Upvotes: 2

Related Questions