Return Same Vlookup for certain day.

Clive Wallace

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

Clive Wallace

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. 😶

Cary S.

Clive,

My apologies for the confusion, as it seems I didn't edit everything properly in my previous post. The good news is that my MAX/COLLECT formula doesn't rely on date format. However, it does assume there is a Created (Date) column on the sheet referenced by {System Sheet 1 Range 4} and the "Project Start Time/Date" is based upon the Created (Date) column. If this is the case you can use the MAX/COLLECT by:

1. Create a cross sheet reference only the Created (Date) column called {Project Start Date}
2. Create a cross sheet reference to only the "Project No." column called {Project No.}
3. Add the following formula to the "Project Start Time/Date" column: =MAX(COLLECT({Project Start Date}, {Project No.}, [Project No.]@row, {Project Start Date}, <[Project Finish Time]@row)) + ""

Please note that this references the "Project Finish Time" column from your sheet rather than the "Project Finish Time Txt/Date". Also I've published my test sheets with editor permissions here:

1. Source Sheet: https://app.smartsheet.com/b/publish?EQBCT=f8f5289bde7f430d977c4036c962266e
2. Destination Sheet: https://app.smartsheet.com/b/publish?EQBCT=24cf6aac34074731bd29cd3b3ee2ab37

If this doesn't work for you, a screenshot of the sheet with the start dates, highlighting the information you'd like to include in the Project Start Time/Date column would be helpful.

Cary S.

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

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. 😶

Cary S.

Clive,

My apologies for the confusion, as it seems I didn't edit everything properly in my previous post. The good news is that my MAX/COLLECT formula doesn't rely on date format. However, it does assume there is a Created (Date) column on the sheet referenced by {System Sheet 1 Range 4} and the "Project Start Time/Date" is based upon the Created (Date) column. If this is the case you can use the MAX/COLLECT by:

1. Create a cross sheet reference only the Created (Date) column called {Project Start Date}
2. Create a cross sheet reference to only the "Project No." column called {Project No.}
3. Add the following formula to the "Project Start Time/Date" column: =MAX(COLLECT({Project Start Date}, {Project No.}, [Project No.]@row, {Project Start Date}, <[Project Finish Time]@row)) + ""

Please note that this references the "Project Finish Time" column from your sheet rather than the "Project Finish Time Txt/Date". Also I've published my test sheets with editor permissions here:

1. Source Sheet: https://app.smartsheet.com/b/publish?EQBCT=f8f5289bde7f430d977c4036c962266e
2. Destination Sheet: https://app.smartsheet.com/b/publish?EQBCT=24cf6aac34074731bd29cd3b3ee2ab37

If this doesn't work for you, a screenshot of the sheet with the start dates, highlighting the information you'd like to include in the Project Start Time/Date column would be helpful.

Clive Wallace

Awesome that worked thank you

Cary S.

Excellent! Happy to help!