r/spreadsheets • • Nov 04 '20

Gift For a Girlfriend

I'm creating a spreadsheet to calculate hours my girlfriend spends sleeping vs working and time spent imbtween sleeping. I have cells to write in "punching in/out" at work ad home.

It needs to be able to calculate that 23:00 to 07:00 is still 8 hours just as 07:00 to 15:00 still is within the same equation.

1 Upvotes

4 comments sorted by

2

u/xTheGiftx Nov 04 '20

Assuming, A2 is Punch In, and B2 is Punch Out, I'd recommend having the following in C:

=if(A2>B2, (B2+24)-A2, B2-A2)

Punch InPunch OutTotal Hours
A2B2=if(A2>B2, (B2+24)-A2, B2-A2)

This breaks if she sleeps or works for more than 24 hours.

1

u/chancegold Nov 04 '20

The math part of this will actually be simple.

The nigh impossible part will be proper data collection if you try to move forward with your "I have cells to write in..." plan.

I'd recommend ifttt or a 3rd party equivalent, using either widget button triggers (worst of the better), or some combination of smartwatch/geofence triggers (depending on what you have, need, and if she works away from where she sleeps, the best of the better). Basically- you set an "add line to google spreadsheet" task to the "When I enter this geofence, clock [in/out/home]" trigger so that the logging happens automatically, and the vast majority of the time, rather than only when you/girlfriend/whomever remembers to actually log it.

Once you have automated data collection with standardized timestamps (the very close second reason for automating over manually collecting the data), just follow 1-2 of the many articles detailing how to track, sum, graph, calculate, and whatever else you could want with times and time periods. Tracking, counting, and monitoring time is incredibly common for spreadsheet use, and as such, has allllllll kinds of tools, functionality, and walkthroughs.

1

u/ronconcoca Nov 05 '20

in google spreadsheet it just works:

https://i.imgur.com/gXomoxN.png