+ Reply to Thread
Results 1 to 11 of 11

Excel fomula based on two columns with dates

  1. #1
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Excel fomula based on two columns with dates

    Hi All,

    Your help would be appreciated,

    I have a fomula in column C, based on the dates in column F, but i can't figure out what the additional part of the fomula is when Column F is empty but a new date is in column G. and if a new dates is in Column G i need the fomula to state that a new date is acquired.

    Hopefully that makes sense. Excel attahced hopefully clears things up.

    Regards,

    TDF
    Attached Files Attached Files
    Last edited by TDFrench; 06-22-2022 at 06:35 AM.

  2. #2
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Excel fomula based on two columns with dates

    Not sure if you've uploaded the wrong file...

    No formula in Col C, no dates in F & G.

    Hard to try and second guess you from your description and we can't take a look at what you've got already.

  3. #3
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Excel fomula based on two columns with dates

    my apologies, new file uploaded, thank you

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Excel fomula based on two columns with dates

    Is this what you want?

    =IF(D4="complete","Complete",IF(MAX(F4,G4)=TODAY(),"Due Today",IFS(MAX(F4,G4)=0,"Date Required",MAX(F4,G4) < TODAY(),"Overdue",MAX(F4,G4) > TODAY(),"On Track")))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  5. #5
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Excel fomula based on two columns with dates

    unfortunately not, i need it to say if no date is in column F but there is a date in column G for the same row, then it returns "date acquired", whilst keeping all the current arguments as they are.

  6. #6
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Excel fomula based on two columns with dates

    I was looking at your formula in a bit more detail, whilst it's great, dates in column F that are in the past shouldn't be overwritten by column G, so sadly the "Max" whilst nearly a great solution doesn't quite work for what i'm looking for.

  7. #7
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Excel fomula based on two columns with dates

    New Excel file uploaded, that hopefully makes more sense.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Excel fomula based on two columns with dates

    This?

    =IF(G4<>"","Date Acquired",IF(D4="complete","Complete",IF(F4=TODAY(),"Due Today",IFS(F4="","Date Required",F4< TODAY(),"Overdue",F4> TODAY(),"On Track"))))

  9. #9
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Excel fomula based on two columns with dates

    unfortunately not, if i have dates in both columns it will now only say date acquired, when in that instance of dates in both columns, it should be using the first date to say if it's "overdue", "on track" or "due today", and ignorning the second date. it's when when no date is in the first date column but a date in the second column that it should say date acquired.
    Last edited by TDFrench; 06-22-2022 at 08:12 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Excel fomula based on two columns with dates

    This?

    =IF(AND(F4="",G4<>""),"Date Acquired",IF(D4="complete","Complete",IF(F4=TODAY(),"Due Today",IFS(F4="","Date Required",F4< TODAY(),"Overdue",F4> TODAY(),"On Track"))))

  11. #11
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Excel fomula based on two columns with dates

    That works perfectly, thank you, I had tried with Isblank but not the and function , so thank you very much

+ 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. Fomula to show multiple dates in a cell that changes daily
    By msdivers4 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-12-2019, 09:23 PM
  2. Using Dates in the Fomula
    By jr-egal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2017, 01:17 PM
  3. Fomula Help based on Age
    By agroeneveld in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2016, 12:46 PM
  4. [SOLVED] IF Fomula - Based on a table
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2014, 01:21 PM
  5. Replies: 2
    Last Post: 07-10-2012, 02:10 AM
  6. Execute Now fomula based on text in another cell
    By Joker757 in forum Excel General
    Replies: 2
    Last Post: 05-02-2012, 08:20 AM
  7. Fomula to Calculate Multiple Columns with TIED Values
    By lumo1985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2008, 01:23 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