Let's say I want to calculate my overtime in Google Spreadsheets. I need the function to either convert an integer (10) to a duration (10:00:00), or vice versa, to be able to perform a calculation.

So,

=A1 - 8

or

=A1 - 8:00:00

should be converted to

10:30:00 - 8:00:00

and return

02:30:00

in order to continue the calculation in another cell.

upvote
  flag
What is in A1 ? – pnuts
upvote
  flag
There's a time value (duration). – Levano
1 upvote
  flag
If always subtracting 8 hours try =A1-1/3 with custom format of [hh]:mm:ss. – pnuts
1 upvote
  flag
That works, thank you so much! – Levano
upvote
  flag
@pnuts How do I use your fracture calculation for any number? For example, I would like to use 40 instead of 8 somewhere else (but the same way as 1/3... thanks in advance! – Levano
1 upvote
  flag
As @ttarchala mentioned, divide by 24 (8/24 =1/3). Date indices use one day is one unit, so say 12 hours counts as 0.5. – pnuts

1 Answers 11

To convert an integer (or a fraction) representing number of hours to a duration, just divide it by 24. If you put the value 10.5 in A1, then put this formula in B1

=A1/24-"08:00:00"

And format B1 as Duration, you will get 2:30:00 as the cell value. The "real" underlying value, though, will be 0.1041666667 which corresponds to 2.5/24th of a day.

upvote
  flag
But A1 is always a duration (e.g. 07:30:00, for example). – Levano

Not the answer you're looking for? Browse other questions tagged or ask your own question.