CODEWITHSUNDEEP
timegoogle-sheets
jungliztik
jungliztik

Reputation: 21

Summing times in Google sheets

I have a sheet where I record my working hours (this is more for me to remind me to stop working than anything else). For every day, I have three possible shifts - early, normal & late, and I have a formula which will sum up any times put into these columns and give me the daily total hours.

To summarise the duration of time spent working in a day, I use the following formula: =(C41-B41)+(E41-D41)+12+(G41-F41) which is: early end time minus early start time normal end time minus normal start time PLUS 12 hours late end time minus late start time

Which gives me output like this: working week

What I cannot seem to achieve is, the ability to sum the daily totals into something which shows me the total hours worked over 1-week. If I attempt to sum the daily totals together for the example image shown, I get some wild figure such as 1487:25:00 when formatting as 'Duration' or 23:25:00 when formatted as 'Time'!

All my cells where I record the hours worked are formatted as 'Time'

Upvotes: 2

Views: 7490

Answers (1)

ttarchala
ttarchala

Reputation: 4587

When using arithmetic operations on date values in Google Sheets, it's important to remember that the internal representation of a date is numeric, and understood as the number of days since January 1, 1970.

What follows from that, is that if you want to add 12 hours to a time duration, you should not write "+12" because that will in fact add 12 days. Instead add "+12/24". In other words, try the following formula instead of the one you are using now:

=(C41-B41)+(E41-D41)+(12/24+G41-F41)

Upvotes: 1

Related Questions