+ Reply to Thread
Results 1 to 8 of 8

How to subtract date while getting output considering leap years

  1. #1
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    17

    Question 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
    Attached Files Attached Files
    Last edited by jake29; 04-06-2021 at 11:21 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

    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: copy to clipboard
    Please Login or Register  to view this content.
    Format cell as GENERAL
    Does that work for you?
    Last edited by ChemistB; 04-06-2021 at 09:57 AM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    17

    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
    Last edited by jake29; 04-06-2021 at 11:03 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,600

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    17

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

    Thanks for letting me know. I will attach excel file

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,151

    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: copy to clipboard
    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
    Last edited by FlameRetired; 04-06-2021 at 12:13 PM.
    Dave

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,571

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

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

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

  8. #8
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    17

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Expiration Date with Edate accounting for leap years
    By Chris69 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2021, 10:19 AM
  2. leap years and date calculation
    By Alaxus in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-16-2020, 09:34 AM
  3. [SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.
    By sharpmel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-17-2013, 04:20 PM
  4. Date Differences and ignoring Leap Years
    By kieran54055 in forum Excel General
    Replies: 2
    Last Post: 09-20-2010, 11:51 AM
  5. Excel =Date and Leap Years
    By thesonofdarwin in forum Excel General
    Replies: 10
    Last Post: 06-15-2010, 03:31 AM
  6. Problem with displaying a date range for leap years
    By Parmo in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-02-2007, 06:27 PM
  7. [SOLVED] To create formula to add 3 years and subtract 1 day from a date?
    By rostroncarlyle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-08-2005, 07:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1