+ Reply to Thread
Results 1 to 5 of 5

Days remaining/overdue formula

  1. #1
    Registered User
    Join Date
    10-18-2020
    Location
    Sanford, NC
    MS-Off Ver
    2016
    Posts
    3

    Unhappy Days remaining/overdue formula

    I am trying to make a status sheet for aircraft that reflects time remaining on replacements, inspections etc. I have accomplished this base on hours of operation and cycle times but the calendar items are throwing me for a loop. based on what i've found so far, this formula, =IF(H20<B4,B4-H20,B4-H20) returns an accurate number in days:

    H20= 11/10/1986
    B4= 11/1/2020 (FUTURE DATE)
    RESULT= 12410.00 (34 YEARS)

    How can i have the result display a negative (-) in front of this number to show that this number is overdue?
    How can i change this formula to also calculate "days remaining" without a negative

    i would like for this to be displayed in the same cell, as this is the way i am copying the information off that looks like a complicated database output
    Lastly, i would like to change the output to whole months, so the number is not so scary.

    Thanks for any advice you can give me, i have attached the file, i think

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Days remaining/overdue formula

    The file didn't make it...


    Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-18-2020
    Location
    Sanford, NC
    MS-Off Ver
    2016
    Posts
    3

    Re: Days remaining/overdue formula

    adding spreadsheet
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: Days remaining/overdue formula

    Does this give you what you want? Put this in cell J20 and copy it down:

    =IF(H20>$B$4,DATEDIF($B$4,H20,"M"),-DATEDIF(H20,$B$4,"M"))

  5. #5
    Registered User
    Join Date
    10-18-2020
    Location
    Sanford, NC
    MS-Off Ver
    2016
    Posts
    3

    Re: Days remaining/overdue formula

    Gregb11,
    Thank you that is exactly what i needed.

+ 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. Formula to Calculate Remaining Days without Weekends
    By Katherine_77 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2019, 09:30 AM
  2. Excel 2010 - Formula for days overdue HELP!
    By banks14 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2014, 02:31 PM
  3. Formula to count days overdue 5, 10, 15, 20, 25, 30, 60, 90+
    By clintwade in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2013, 10:37 AM
  4. how to make a formula in remaining days
    By RodelZkie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-24-2013, 05:49 AM
  5. excel formula: days remaining=end date-todays date+extention days
    By fsprings in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-04-2013, 06:45 AM
  6. [SOLVED] Formula for Days Overdue if not Completed
    By excelnewb02 in forum Excel General
    Replies: 5
    Last Post: 09-11-2012, 04:51 AM
  7. What is the Formula for Days remaining between 2 dates?
    By Fayt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2010, 11:53 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