# How to subtract date while getting output considering leap years

1. ## How to subtract date while getting output considering leap years

Hi,

I have an application where I Need to Input the Date of Birth of a Member, who has turned 18 years in the last 30 days. So, if I add 07 March 2003
as Date of Birth of member then he would be turning 18 years in the last 30 Days while considering today's date as 06-April-2021 But the problem in column C I notice that it fails to show the correct date. Is there a formula that considers leap years and shows the value in C while considering leap year?

All Below Dates in DDMMYYYY Formats

 Column A Column B Column C Todays Date Member Turned 18 Years in Last 30 days from Today's Date DOB Based on B2 -Days(18 Years) 06-04-2021 18:40 07-03-2021 18:40 12-03-2003 18:40 =Now() =A2-30 =B2-(365*18)

Thank You

2. ## Re: How to subtract date while getting output considering leap years

Not able to decipher your table. It would be easier if you posted an example workbook (see yellow banner at top of page for instructions).
One note. If you are just looking for dates, use TODAY() instead of NOW(). Now() includes a time.

Edit: I see you reformatted. That helped.
I'm not following your logic. If the DOB could have occurred any date within the last 30 days, how do you determine the number of days.

If you are using the date 30 days in the past to calculate the # of days (i.e. B2),
you can use
Formula:
`Please Login or Register  to view this content.`
Format cell as GENERAL
Does that work for you?

3. ## Re: How to subtract date while getting output considering leap years

I tried as suggested, =A2-DATE(YEAR(B2)-18,MONTH(B2),YEAR(B2)). I have replaced A2 instead of B2 as In A2 i have 30 Days, in A3 I have 20 Days so on.
Also, set the cell as general but it shows a negative number. it shows for the above example as -39702

4. ## Re: How to subtract date while getting output considering leap years

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

5. ## Re: How to subtract date while getting output considering leap years

Thanks for letting me know. I will attach excel file

6. ## Re: How to subtract date while getting output considering leap years

Perhaps I am not interpreting your request correctly, but it appears that this is the formula you need in column D (D2:D4)
Formula:
`Please Login or Register  to view this content.`
It returns the desired birth dates indicated, but it's a fraction of a day off ... (hence the time of 15:14). If you are not aware of it the average number of days in a year (accounting for leap years AND end of century years non leap years) is 365.2425

 A B C D E 1 X Days Todays Date Member Turned 18 Years in Last X days DOB Based on C2 -Days(18 Years) Added as Suggested 2 30 4/6/2021 0:00 3/7/2021 0:00 3/7/2003 15:14 -39702 3 31 4/6/2021 0:00 3/6/2021 0:00 3/6/2003 15:14 4 29 4/6/2021 0:00 3/8/2021 0:00 3/8/2003 15:14

7. ## Re: How to subtract date while getting output considering leap years

In D2 (E2 in your example sheet) copied down
Formula:
`Please Login or Register  to view this content.`

I also changed your formatting to remove TIME from the dates.

8. ## Re: How to subtract date while getting output considering leap years

Thanks chemistB, initially I tried the same formula what you gave me, i altered it reflect what you have provided =DATE(YEAR(C2)-18,MONTH(C2),DAY(C2)) and also set the cell to general it did not work for me.

may be is it because there was Time attached to it that's the reason?

Anyhow it worked for me E2, is what you have suggested and all this while before coming here i had miscalculation because leap year.

Thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)