Date
Hi!
I have a tricky formula I'm trying to calculate, but with no success so far...
I have to different sheets that I'm looking on.
Sheet1: Planning and monitoring sheet
Sheet2: Work reports
The formula challenge:
In Sheet2 our guys at the field are reporting on different predetermined projects.
They report which date they have been working with the different projects, amount of hours + if the project is finished or not (Yes/No).
In Sheet1 I want not just to sum up how many hours we have spent on each project, but I want also the date column "Finished" to automatically show which date the project got finished.
So, is there a way to put a formula into the date cell in Sheet2 that will show which date the guys have reported as finished ("Yes") in Sheet1 ?
The criteria must be: if the project nr is the same + if the column "Finished" is marked "Yes" in Sheet1
Any ideas?
Best regards,
Rickard
Comments
-
i'm not entirely sure I understand, some pictures might help, publishing a copy here would definitely help me understand.
That said if you want to track the amount of time spent on a project it depends on how you have your sheet formatted. You could possibly use a sumif formula, a collect formula, or maybe even a sum(Children()) style formula depending on the arrangement of the sheet.
To parse out the finish date you will need either a vlookup() formula or an index(match()) formula. Again, I need to know more about the sheet to give you a better answer.
-
The date would have to be manually entered on sheet one. Then a simple INDEX/MATCH would pull it to sheet 2.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!