Return Same Vlookup for certain day.

Basically what im trying to do is a project tracker. When the guys do a project they spend a certain amount of hours on it. thats all good until the project is paused and restarted again i have a issue where it calculates off the closest date. So what I tried to do was if the project start was on the same day as the finish it would use that date.

Attached is a photo of my original sheet. You will see its stuffing up

See how the hours become minus hours. (The Project Start time/date is pulled from another sheet).It in the photo above uses the simple formula =VLOOKUP([Project No.]3, {System Sheet 1 Range 2}, 4, false)

So in an attempt to rectify the issue I used the formula

=IF(AND(LEFT([Project Finish Time txt/Date]3, 2) = LEFT({System Sheet 1 Range 4}, 2), VLOOKUP([Project No.]3, {System Sheet 1 Range 2}, 1, false) = [Project No.]3), VLOOKUP([Project No.]3, {System Sheet 1 Range 2}, 4, false)) what i am trying to do in this formula is if the project started on the same date it ended uses that date to calculate the hours. Not the new date generated when the boys press start project again the next day. And the project no.s match

I got #invalid data type

Any Ideas?

Apologies for my bad grammar

Best Answers

  • Clive Wallace
    Answer ✓

    Carey,

    Just a starter im a Aussie so the date works different we read today as 5/04/2020. Thats why I was trying to use LEFT(REF,2) as the first two numbers are the day in the in the month for us down under.

    So for the formula

    =MAX(COLLECT({System Sheet 1 Range 4}, {System Sheet 1 Range 5}, [Project No.]@row, {System Sheet 1 Range 4}, <[Project Finish Time txt/Date]@row)) + ""

    I get 0.

    =MAX(COLLECT({System Sheet 1 Range 4}, {4/4/20 System Sheet 1 Range 5}, [Project No.]@row, {System Sheet 1 Range 4}, <[Project Finish Time txt/Date]@row)) + ""

    I get #invalid ref

    So just to clarify im trying to get the hours from a certain matching project No. that started and finished on the same day.

    So for example you are building a small boat. The project will probably last three weeks so every day you enter the project number and this is entered on a sheet. You don't want it to calculate off the hours after you end the day so you enter the project number again and press end. this automatically calculates the hours for that project that day. But when you start the same project the next day again it automatically uses that created time to calculate the start of yesterdays day, instead of the the earlier one the day before. Hard to explain. 😶

Answers

  • Cary S.
    Cary S. Employee
    edited 04/04/20

    Hello Clive!


    If I'm thinking about this correctly, you would like to return the "largest" date in a system column that's less than the one in the system column on a the row for a specific project. You can start by using COLLECT to bring together all the rows with the same project number and a project start date that's before the project finish date.Then you can use MAX to find the "highest" or most recent value. On my test sheets the following formula worked:

    =MAX(COLLECT(Same Day Lookup Project Start Time/Date}, {4/4/20 Same Day Lookup Source Project No.}, [Project No.]@row, {Same Day Lookup Project Start Time/Date}, <[Project Finish Time/Date]@row)) + ""

    {Same Day Lookup Project Start Time/Date} is the system column on the source sheet with the start time/date.

    {Same Day Lookup Source Project No.} is the Project No. column on the source sheet.

    + "" is used to turn the result into text.


    The nice thing about using the COLLECT in this instance is that you can add additional ranges and columns in the event you'd like to filter by the person who is assigned.

  • Clive Wallace
    Answer ✓

    Carey,

    Just a starter im a Aussie so the date works different we read today as 5/04/2020. Thats why I was trying to use LEFT(REF,2) as the first two numbers are the day in the in the month for us down under.

    So for the formula

    =MAX(COLLECT({System Sheet 1 Range 4}, {System Sheet 1 Range 5}, [Project No.]@row, {System Sheet 1 Range 4}, <[Project Finish Time txt/Date]@row)) + ""

    I get 0.

    =MAX(COLLECT({System Sheet 1 Range 4}, {4/4/20 System Sheet 1 Range 5}, [Project No.]@row, {System Sheet 1 Range 4}, <[Project Finish Time txt/Date]@row)) + ""

    I get #invalid ref

    So just to clarify im trying to get the hours from a certain matching project No. that started and finished on the same day.

    So for example you are building a small boat. The project will probably last three weeks so every day you enter the project number and this is entered on a sheet. You don't want it to calculate off the hours after you end the day so you enter the project number again and press end. this automatically calculates the hours for that project that day. But when you start the same project the next day again it automatically uses that created time to calculate the start of yesterdays day, instead of the the earlier one the day before. Hard to explain. 😶

  • Awesome that worked thank you

  • Cary S.
    Cary S. Employee

    Excellent! Happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!