+ Reply to Thread
Results 1 to 22 of 22

Checking for Latest Date & Adding 365 Days

  1. #1
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Checking for Latest Date & Adding 365 Days

    I need help with basic functions over 3 columns. I have col C with a date and I have col E which shows the date of col C plus 365 days. There can also be a more recent date in col D and I need this date plus 365 days in col D as it is newer than the date in col C.
    Last edited by AliGW; 05-15-2020 at 10:50 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Basic function

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I will do it for you this time.)
    Last edited by AliGW; 05-15-2020 at 11:04 AM.
    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.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    Try this:

    =IF(D2>C2,D2,C2)+365

  4. #4
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    Sorry I don't know how to show the spreadsheet in this box. I have gas service dates in column C. Column E shows the service date of column C plus 365 days. There can be 2 services carried out in one year so the extra service date is showing in column D, as this date is the most recent date then I need column E to show this date plus 365 days instead of the earlier date in column C, hope this explains it.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    Did you try what I suggested in post #3? If it doesn't work, attach your workbook.

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

  6. #6
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    Thanks you for your help, this works great.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    Thank you I'll do that, just one more thing please.
    I have this function in column F =IF(E11-D11<365,"Yes","NO")this is
    telling me if the service dates were within a year, can column F show as
    blank if there isn't a date in Column E.
    The dates will be filled in later in the year.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    Ys - like this:

    =IF(E11="","",IF(E11-D11<365,"Yes","NO"))

  10. #10
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11
    Thank you so much for your help
    Last edited by AliGW; 05-15-2020 at 04:14 PM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  12. #12
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    =IF(D2>C2,D2,C2)+365 this works if D2 is greater than C2 + 365 gives me the next service date but sometimes there are extra services. Can I add another column, eg if D2>C2 and G2 is greater than both + 365

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    Yes:

    =IF(AND(D2>C2,G2>D2),G2,IF(D2>C2,D2,C2))

  14. #14
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    Thank you nearly there. The following columns have these service dates -
    C2 = 29/03/19
    D2 = 17/03/20
    E2 = 18/04/20
    G2 = 05/05/20
    H2 = next service date (+365 days)

    So column H should show the date +365 days of the most recent date of the 3 columns which is G2 +365.
    If there was no date in col G2 then the date in H would be the date in E2 +365.
    If there was no date in either G2 or E2 then col H would be the date in D2 +365

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    Try this:

    =MAX(C2:H2)+365

  16. #16
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    =MAX(C2,D2,E2,F2)+365 this worked - thank you so much for helping.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    No worries!

    As a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  18. #18
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    latest date

    I have 2 columns with service dates which should be done within 365 days. I have Col H where I ask the question - was the service done within 366 days - Yes or No.
    The equation I currently use is =IF(E2="","",IF(E2-D2<366,"Yes","NO")) and this works but I need to factor in a third date in col F.


    Col E is first date, Col D is 2nd date and Col F is extra date.

    The most recent service date (which might be in col E or F)is the important date.

    I need add to the equation above to factor in the third date col F. Col F supercedes Col E.

    Can you help

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: latest date

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

  20. #20
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    As you can see in the attached spreadsheet I need col H4 to show that F4 minus C4 is a no answer as it is overdue bu more than 366 days.

    I have tried to attache the spreadsheet
    Attached Files Attached Files

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,554

    Re: Checking for Latest Date & Adding 365 Days

    Maybe this:

    =IF(MAX(E2,F2)<366,"Yes","No")

  22. #22
    Registered User
    Join Date
    05-15-2020
    Location
    Strathclyde, Scotland
    MS-Off Ver
    10
    Posts
    11

    Re: Checking for Latest Date & Adding 365 Days

    Thanks for all your help

+ 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] IF Function Help (Basic)
    By Nerd_04 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2017, 06:26 AM
  2. Need Help with Function Getting Number of Rows from Specified Sheet
    By alacey425 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2016, 09:20 PM
  3. [SOLVED] Basic IF function
    By JaGil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-28-2014, 07:03 AM
  4. Basic If Function problem
    By MelodyK in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 04-28-2014, 06:37 PM
  5. Help with a basic function
    By Rhuumz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2013, 02:05 AM
  6. I need a basic function
    By Elizabeth.gall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2008, 02:43 PM
  7. Basic IF function
    By Intern in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2007, 12:44 PM

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