+ Reply to Thread
Results 1 to 3 of 3

Date Calculation not working

Hybrid View

  1. #1
    Registered User
    Join Date
    01-18-2023
    Location
    Bc Canada
    MS-Off Ver
    2019
    Posts
    2

    Date Calculation not working

    We have a sheet that has all of the formulas working, I have verified that the formula is the same by copy and paste, yet all of a sudden this last line will not calculate the birthdate correctly.
    2023-01-11 2020-01-08 3y,0m,10d 3y1
    2023-01-11 2022-12-15 0y,0m,10d 0y2

    First date is the date they contacted us, second date is the date they were born first line works, second line does not. If I change the 2023-01-11 to anything after the 16th, 2023-01-2016 it works. But if I change it to a date of contact before Jan 16, it breaks and tells us the child is 10 days old. There are multiple lines above that work find, its just this newest one at the bottom that isn't working. Ive tried copy and pasting the formulas from the lines above and it still breaks.

    Here are the two formulas for the age that works and age that doesn't, in order

    =DATEDIF(F13,E13,"Y")&"y,"
    &DATEDIF(F13,E13,"YM")&"m,"
    &E13-DATE(YEAR(E13),MONTH(E13),1)&"d"


    =DATEDIF(F14,E14,"Y")&"y,"
    &DATEDIF(F14,E14,"YM")&"m,"
    &E14-DATE(YEAR(E14),MONTH(E14),1)&"d"

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Date Calculation not working

    Can you specify what you expect to see? You're constructing a date from the year and month of E13 and then subtracting that date from E13. What do you expect as the result? What is the logic in words?? I don't understand why the year and month are involving the date in F13, but for the day calculation you don't use F13, you only look at E13. Maybe that is the problem??
    Last edited by teylyn; 01-18-2023 at 07:50 PM.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,176

    Re: Date Calculation not working

    Try this
    =DATEDIF(F13,E13,"Y")&"y,"&DATEDIF(F13,E13,"YM")&"m,"&DATEDIF(F13,E13,"MD")&"d"
    Or

    =DATEDIF(F13,E13,"Y")&"y,"&DATEDIF(F13,E13,"YM")&"m,"&E13-EDATE(F13,DATEDIF(F13,E13,"M"))&"d"
    Last edited by Phuocam; 01-18-2023 at 08:11 PM.

+ 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. [SOLVED] Date Calculation + 5 / 20 working days depending on what is within a cell
    By digbee in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 01-08-2021, 11:05 AM
  2. Due Date Calculation by taking only working hours and days
    By Prashanth Gangala in forum Excel General
    Replies: 13
    Last Post: 10-10-2017, 05:15 PM
  3. [SOLVED] Require VBA working to construct the summary sheet with the help of date calculation
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-24-2013, 11:31 PM
  4. Replies: 6
    Last Post: 05-20-2013, 07:45 PM
  5. Working on Date /time calculation
    By birims in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2012, 03:20 AM
  6. Due date formula - 13-working day SLA calculation
    By einrobinator in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-06-2012, 10:01 AM
  7. Date/Time Calculation excluding weekends and holiday list not working
    By mikeyk80 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2012, 10:39 AM

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