Dear Spreadsheet Whisperer: Date Me?

Today we have a very special addition to the DSW cannon, fresh from the Google Drive of Mr. Spreadsheet Whisperer, who came to me with a forlorn expression on his cute lil face when his DIY time sheet was miscalculating his paycheck. What should we do about this?!?!

I am so proud of MSW. He made a spreadsheet time tracker with his collaborative teacher (they’re really good at spreadsheets btw!) where they put in start time, end time, and a formula field to calculate duration of his work shift. This is a more accurate representation of their work than simply typing a number in, and I think it makes sense for all sorts of reasons. The design looked something like this:

Everything looks pretty good so far, but like many spreadsheet projects…

[Image description: three femme singers from the band The Chicks (fka The Dixie Chicks) stand in a muted industrial building and lean on an old car. Superimposed over the image is the quote “Answers only make more questions”

Yes, I am quoting The Chicks, one of the best bands of all time IMO!

(Sidenote – WordPress let’s us write text over images now!!!! This is a game changer!)

Have you ever been in this situation, where you flex your spreadsheet muscles and get 90% there only to get inexplicably super stuck? Yeah, me too. Super frustrating, but also super unstuckable! As my co-conspirator likes to say, spreadsheets are 90% mindset and 10% skillset.

Here’s what happened next:

Column F is a simple formula of “Duration” (Column D, in this case, 2 hours) times “Rate of Pay” (Column D, in this case, $15/hour). So the Pay Amount should be $30 but it is coming in as $6. Weird!

Also, yikes!!! I want MSW and his pal to earn more than $3/hour on his curriculum development work! I mean, public school teachers are undervalued but not THAAAAAT bad.

To solve this problem, I had to dive deep into the annals of date/time fields in Google Sheets. It was a fun exercise and I’m excited about what I learned. This one was pretty tricky, too – the first couple of sources that I consulted did not give me the information that I needed.

Instead of a screenshot, I’m going to copy and paste the key answer that I found (screenshots are not compatible with screen reading technology).

The easiest way is to represent the time periods using regular numbers and decimals instead of in the time format. So, for example, you could format the time period as 1, 1.25, 1.5, 1.75, etc. This works well if you don’t need very fine granularity and are looking for the fastest solution. I personally recommend doing it this way because this is usually how I see calculations done (ie, people input “one and a half hours” as 1.5 hours into a payroll sheet). If you are having other people input their time, this might be easier.

Another way is to represent the time period as time (like on a clock). If you run into issues with the time formatting in Google Sheets, multiply the time by 24. The “time” format in Google Sheets is stored as a fraction between 0 and 1. So, to rectify this, multiply it by 24. So, your formula should look like this: =(A1*24)*B1, where A1 is where you’re storing the time and B1 is the hourly rate.

Both of these allow you to enter the time in one cell. If you’re okay with using multiple cells, you could have one column for hours, one for minutes, and one for the hourly pay. So, for example, 1 hour and 30 minutes could be represented by A1 = 1, B1 = 30, and C1 = the hourly rate. From there, your formula would look like this: =(A1+(B1/60))*C1. B1/60 converts the number of minutes into a fraction of an hour. Add that to the number of hours to get the total amount of time worked. Multiply that by the hourly rate to get the amount of money earned.

Elynn Lee, answering this question on Quora

Ultimately, adjusting the formula to multiply by 24 is exactly what we had to do to get the right answer! Such a “simple” solution that could have been agonizing to figure out with ratios!

Why this solution matters

Here’s why I think this is such a special story to share with the TDAA community —

1. MSW asked for help instead of giving up (resilience!)
2. MSW knew that technology could help, but he didn’t know how to make the technology do the thing he needed (imagination!)
3. MSW decided to find a solution instead of settling (high expectations!)

Can you relate to the temptation of option 3? I certainly can! There are so many times when I think, “well it’s not thaaaaaaaat inconvenient, I’ll just do xyz from now on!” I’m not embarassed to say that I’ve come up with some WACKY, time consuming, workarounds that I’ve gone to great lengths to hide (like cleaning up data once a month before the reports get sent, instead of fixing the root problem).

Plus, it can be so, so hard to make the call about WHEN to invest the time. Because if I automate every little thing, or if I do a technical deep dive on every issue I encounter, then I might never get the big stuff done. But I think the alternative is often more toxic on the Left, which is accepting systems that don’t really work, which waste our time, erode our confidence, damage our person-tech experiences, and limit our imaginations for change.

So even though it might seem like a small thing – to correctly calculate this formula field – to me it’s a big thing. No victory should go un-celebrated.

Speaking of Mr. Spreadsheet Whisperer,

When MSW and I first started dating (start date: 10/31/2016 – present… you’re on your own to calculate the duration and the value on your own), I was at the very beginning of what would become a career change, from sustainable agriculture activism to nonprofit database badassery (that’s the TECHNICAL TERM, OK!?). Soon-to-be Mr. Spreadsheet Whisperer would come to my tiny, third floor walkup apartment and patiently write lesson plans while I took online courses on spreadsheets. I remember long nights of copying-and-pasting because I was intimidated to learn VLOOKUPS and my system was “working” just fine. I made the leap to visionary spreadsheet building with support from MSW and now I love to return the favor by smoothing out little bumps with him and all of you.

Those long, quiet nights of bumping elbows as we clicked away on greasy keyboards, well, it turns out that The Chicks sing about that too, in the same song that I quoted above.

Easy silence that you make for me

It’s okay when there’s nothing more to say to me

And the peaceful quiet you create for me

And the way you keep the world at bay for me

“Easy Silence” by the Chicks

We do a pretty good job of creating that “easy silence” … me by quieting the spreadsheet frustration (ok and I do a bit more than that around the house) and him “keeping the world at bay for me.” And all throughout, hitting the keyboard when the sun goes down.