+ Reply to Thread
Results 1 to 11 of 11

Most current date in a row

  1. #1
    Registered User
    Join Date
    04-25-2017
    Location
    Oklahoma city, Ok
    MS-Off Ver
    2010
    Posts
    14

    Most current date in a row

    I need to search a row for to dates M4 or N4
    and subtract it from my start date and calculate number of days.

    Start date is J4 some rows will only have 1 date(M4)
    and other will have 2 dates (M4 & N4) I would need the number of days from J4 to N4

    If the attachment works then that should explain it a lot better.
    Thanks in advance.

    Brad
    Attached Files Attached Files
    Last edited by abadbrad; 01-13-2021 at 03:04 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Most current date in a row

    abadbrad,

    Looking at your HL Project (2) sheet, which column does the number of days go into?
    And as none of the nine records have an RFP e-mail date in Col J, will you use the PKO Email Issued (Start) date in Col L as the Start Date?

    Ochimus

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

    Re: Most current date in a row

    If the attachment works then that should explain it a lot better.
    No, it doesn't.

    You say Start Date is column J, but the sheet says "RFP Email Date". Is that what you're using for a start date? and in your example, there are no dates in that column.
    In what cells do you want your answers?
    Where are the mocked up answers - please put in the answer you expect to get from the formula.
    It might be better to walk us through the logic specifically for 1 row, and then maybe others.

  4. #4
    Registered User
    Join Date
    04-25-2017
    Location
    Oklahoma city, Ok
    MS-Off Ver
    2010
    Posts
    14

    Re: Most current date in a row

    I am sorry, the wrong attachment got uploaded.
    I also revised the instructions.

    I need to search a row for to dates M4 or N4
    and subtract it from my start date and calculate the number of days in O4.

    The start date is J4. Some rows will only have 1 date(M4)
    and others will have 2 dates (M4 & N4).
    I would need the number of days from J4 to N4 to calculate in O4

    If the attachment works, then that should explain it a lot better.
    Thanks in advance.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Most current date in a row

    Put this in O4 and copy down:

    =IF([@[Actual CD Date ]]="",[@[Project CD Date]]-[@[Start Date]],[@[Actual CD Date ]]-[@[Start Date]])

    (If there is no date in Col N, deduct Col J from Col M, otherwise deduct Col N from Col J)

    Ochimus

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Most current date in a row

    In O4

    =MAX([@[Project CD Date]],[@[Project CD Date]],[@[Actual CD Date ]])-[@[Start Date]]
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

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

    Re: Most current date in a row

    kvsrinivasamurthy has a nice formula, I believe they put in an extra reference by mistake. Just a slight change to it:

    =MAX([@[Project CD Date]],[@[Actual CD Date ]])-[@[Start Date]]

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513
    Quote Originally Posted by Gregb11 View Post
    kvsrinivasamurthy has a nice formula, I believe they put in an extra reference by mistake. Just a slight change to it:

    =MAX([@[Project CD Date]],[@[Actual CD Date ]])-[@[Start Date]]
    Thanks for correction

  9. #9
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Most current date in a row

    Gregb11,

    The user asked for a formula that used the Actual date if it was available.

    In your formula, if a row has both a "Projected" and an "Actual" date that is earlier than the Projected, it will use the Projected (because that has the highest vaiue)

    Ochimus

  10. #10
    Registered User
    Join Date
    04-25-2017
    Location
    Oklahoma city, Ok
    MS-Off Ver
    2010
    Posts
    14

    Re: Most current date in a row

    Thank you so much.
    I getting better at excel and formulas. You saved so much time.
    Ochimus you are correct kvsrinivasamurthy, your formula was the one I wanted.
    Gregb11 I'm still going to play around with yours and see what that does.
    I never copy formulas I always type what you guys suggest.

    Thanks Again!!!!

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

    Re: Most current date in a row

    For the record, all I did was correct kvsrinivasamurthy's formula. They had the same value in the max formula twice and I just removed one

+ 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] Finding the most current and previous current date based on multiple criteria
    By tkbuc in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-27-2016, 06:06 PM
  2. [SOLVED] copy and paste in current active cell, and need current date then scroll down 140 lines
    By vengatvj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2013, 03:40 AM
  3. Code to insert current date (but NOT current time)
    By mjwillyone in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2013, 09:37 AM
  4. [SOLVED] Need average of days for date values from date to current day that updates current date
    By FinGhost in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2013, 04:41 PM
  5. [SOLVED] add current time (hh:mm:ss) as well as current date (dd:mm:yyyy) into the code
    By papasideris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2012, 02:29 PM
  6. adding current date then after saving the file current date function stops
    By gotbitey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2011, 02:15 PM
  7. [SOLVED] Can I automatically enter the current date or current time into a
    By Ben in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 10-19-2005, 11:05 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