Hello All,
I am an amature architect wanabe who helps a few local friends redesign homes in the neighborhood and submit plans to the city for approval. I create projects and use time tracker to record time automatically. I find that during the creative process I create multiple plan and layout files by backing-up or copying my current file. I ended up with 34 files in my current project. It seems each file in a project tracks its own time and when you make a copy or backup, the old and new files have duplicate entries. I have my timer set to quit after 5 minutes of non activity.
You can download the Start, End and Elapsed time from each file into a text (.csc) file but then you would have to eliminate duplicates to try to calculate the total hours you spent on the project. So I downloaded all the files and gave them unique names so I could identify them. Then I decided to try using ChatGPT to see if I could get it to do the work. The results amazed me.
First I downloaded the time files from Chief Architect to a folder:
Then I opened ChatGPT and used the following prompt. You could tailor it to your needs.
"I am going to provide you with a set of .csv files. Each one has three columns. Column A is the Start Date and Time. Column B is the End Date and Time. Column C is the Elapsed Time. My objective is to combine all columns into one spreadsheet, aligning rows that have identical Start Date and time AND identical End Date and Time. Then I want add a set of 4 columns on the left side of the combined spreadsheet that show a single unique set of Start Date and Time and End Date and Time and Elapsed Time. The files I am loading have overlapping entries that I am
trying to eliminate. I just want one set of values that show ALL unique start and stop and elapsed times. I want to sum the elapsed time and multiply it times a rate of $80 per hour and calculate the total amount. I will load the files when you ask me to."
and I got the response:
Yes. Please upload all of the .csv files together, and I’ll build the consolidated spreadsheet for you.
It only let me load 20 files at a time so I told it to wait until I told it to calculate. I highlighted and dragged the first 20 files into ChatGPT and then the other 14. Then I told it to proceed with the calculation.
It only took a minute but it combined all files, analyzed them, added a UNIQUE set of Start, End and Elapsed times from the combined values in all files on the left side of a new spreadsheet, totaled the UNIQUE elapsed time, told me how may unique time periods there were, listed them in Date/Time sequence and calculated the total amoun! Pretty amazing.
This was the final message from ChatGPT:
Completed. I processed the 34 unique CSV files, excluding the duplicate (1) files as requested.
The final results are:
34 source files
279 unique Start/End combinations
255.1608 total unique hours
$20,412.87 total at $80/hour
0 unreadable/unparsed rows
The spreadsheet keeps the four consolidated columns on the left and aligns each of the 34 source files to the right based on an exact match of both Start Date/Time AND End Date/Time.
Download the final 34-file consolidated spreadsheet
final_consolidated_34_files.xlsxSpreadsheetOpen file
Here is a partial screenshot of the final output.
Please try this yourself and give me some feedback on your results. I think the results are correct but I may have missed something. Except for the hassle of downloading each of the files, this has saved me days of work trying to use Excel or some othe method to get the results I was looking for.
Thanks
Greg
(Costa Mesa, CA)
Thanks for the reply. Yes. I do add meetings, calls, measuring time, etc. when billing. This was just a way to aggreagate design hours on CA and eliminate duplicates. Thanks