Hi Everyone!! I’m Alice Keeler, outside of Tiller my preferred title is “Queen of Spreadsheets.” I thought it would be fun for us to have some helpful tips around using spreadsheets that may or may not directly pertain to Tiller. I know many of us are enthusiasts, or beginners who want to know more.
Today’s tip is a quick way to track time.
I posted a video to socials today with this tip, but I thought I would elaborate here.
I created a simple spreadsheet with a column for task, time started, time ended, and time spent.
Please feel free to make a copy of the sample spreadsheet.
Quick & Dirty
Subtract the two date fields and multiply by 24.
In the spreadsheet, column B is the start time. Column C is the end time. =(C2-B2)*24
This will give you the fraction of time in hours.
You can make this fancier by including an IFERROR =IFERROR((C2-B2)*24)
If the values in B2 and C2 are not date and time fields, it will return a blank value.
The above link forces a copy in Google Sheets. If you do not have a Google account you can view the spreadsheet (and download as Excel) by clicking here.
@TillerAlice Hello Alice, Welcome to Tiller! It is great to have you on the Tiller team and active in the user community forums. The collective contributions and support in these forums by users and the Tiller team make Tiller a special community in the financial space. I look forward to your ideas suggestions and solutions both in the main template and community solutions.
Hey Alice, maybe you’ll enjoy this response tip from a minor Duke of Spreadsheets.
I’ve started putting all such formulas in the column header rather than copying/pasting a formula down the column. Putting it in the header avoids the inevitable problems that happen when rows are added/moved or (the horror) values are moved around using Cut/Paste, which produces #REF! errors in those formulas. In this case, here’s what I have in the Time Spent column header (D1):
It puts “Time Spent” in that cell and then creates the values for the whole column below it. ARRAYFORMULA often works, but for more complex cases (like use of XLOOKUP on each row), MAP or BYROW can be used.
I also use ArrayFormula. Good point about adding formulas in the header! A great way to avoid having to re-input formulas repeatedly.
One of my goals here in the community is to consider how to provide a helpful tip in a thread for those who may be reading the thread, not necessarily explaining to the poster.
If anyone wants a quick breakdown of how this formula works:
The formula starts with ={} which is a way to combine multiple values into a single range.
Here, it’s being used to set the column header at the top and apply a formula to all the rows below it. The first value inside the curly braces is “Time Spent”, which acts as the header. The semicolon inside {} tells Google Sheets to place the header in the first row and then continue down the column with the rest of the formula.
The ARRAYFORMULA function makes sure that the calculation applies to every row automatically instead of having to copy the formula down manually.
The IF(B2:B=“”, “”, …) part checks if the Start Time column (B) is empty. If it is, the formula returns an empty cell instead of trying to do a calculation. This helps keep things clean and avoids errors.
The C2:C - B2:B part subtracts the start time from the end time, giving the difference in days. Since time in Google Sheets is stored as a fraction of a day, multiplying by 24 converts it into hours.
The IFERROR(…) function is included as a safety net. If something goes wrong, like a missing value or an invalid entry, it prevents an error message from appearing and just returns an empty cell instead.
