Hey there! I feel like I have a complex issue ahead of me. I am fairly proficient in excel, but trying to marry all these together seems to be something I cannot wrap my head around. To add to that, in the end, this data will need to be in google docs, so macros and some formulas will not work. The plan is below, any input would be much appreciated, thank you!
Google Form - users will submit a form containing a job number, their name, a record type, and a task. All from a drop down so data is always the same except for the job number which is 6 digits. Google forms automatically adds a cell with date and time to the submission of the form. The record type will be either "Start" or "Stop".
Schedule - This will be a list of open jobs containing a due date and time, number of hours needed to run the job, number of hours left till the due date (using today function), hours worked on the job, and hours needed to complete the job. I need a formula that will not only calculate the time between a "start" & "stop" record for the matching job number from the google form sheet, but also add those times up for every set of "start" & "stop" records providing a total number of hours worked on a given job. Some of the constraints/issues are the google form record get added to the top of the spread sheet, so the start record would be row 1 then when the stop record is put in, that gets to row 1 and the start is below it at row 2. Another is there could be multiple start/stop records in a given day for the same job. Additionally, there are other people working on other jobs at the same time, so a start and stop record for 1 job will not always be 2 joining rows on the spreadsheet.
Report - Lastly, I need a sheet that when a job number is entered, it will automatically list all start/stop records pertaining to that job. I have a formula that will provide this data based on the Job Number, the only thing I wanted to add if possible is to provide them in order of date/time for a report. Currently, the formula provides all the row numbers in an array that a job number exists, it pulls that information top to bottom wherever they fall on the sheet.
I know its a lot of information, but again I really appreciate any input. Thank you again!!
Bookmarks