We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Time calculation - which formula is needed? [closed]

asked 2015-09-05 08:18:31 +0200

ROSt52 gravatar image

updated 2020-07-28 13:59:00 +0200

Alex Kemp gravatar image

I sum up times and the sum goes beyond 24h and need to multiply the hours with an hourly rate to create an invoice.

  • Until 23:59 I can use the formula =(HOUR(G15)+MINUTE(G15)/60)*320 to linearize the time
  • Beyond 23:59 the used formula reduces the time value by 24h. This is the correct functionality of the HOUR(cell) function.

I searched Help and Help Wiki but it seems that I overlooked the solution.

I appreciate any help which lets me to linearize hours and minutes beyond 23:59.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-28 13:59:39.098453

2 Answers

Sort by » oldest newest most voted

answered 2015-09-05 10:22:01 +0200

karolus gravatar image

updated 2015-09-05 10:26:33 +0200

Internally Date/Time-Values are stored in units of Day
for transfer into units of hours you need to multiply by 24

=G15*24  * hourly_rate

simply does the job

edit flag offensive delete link more


Thanks for the hint. In Japanese I now need to say "hazukashii"... meaning I feel ashamed because I did not come up with the idea myself.

Hopefully it helps some other guys.

ROSt52 gravatar imageROSt52 ( 2015-09-05 14:44:50 +0200 )edit

answered 2015-09-05 11:56:36 +0200

You also may have to use separate formula for each side of the Day, and add them together, (day1)+(day2), to display your total time, then multiply by your rate for funds.

simple for auditors and bosses to see.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-09-05 08:18:31 +0200

Seen: 1,789 times

Last updated: Sep 05 '15