I am recording my time spent on a project in google excel sheet. There is a column which does addition of the recorded time and output total time to column say D40. The output time looks like <hoursspent>:<minutesspent>:<secondsspend>. For example 30:30:50 would mean that i have worked for 30 hours and 30 minutes and 50 seconds on a project.

Now, I was using this formula to calculate my total invoice

=(C41*HOUR(D40)) (C41*((Minute(D40)/60))) (C41*((SECOND(D40)/3600)))

Where C41 cell contains my hourly rate (say $50).

This is working fine as long as the numbers of hours that i have worked are less than 24. The moment my numer of hours go above 24. The Hour function return the modulus value i.e., HOUR(30) would return 6.

How can I make this calculation generic in a way that it oculd calculate on more than 24 hours value too.

CodePudding user response：

Try

=C41*D40*24

and change formet on the result as $

one hour is part of a day, as you know 1/24th of a day, that's why you could multiply by 24 to get hours, and then multiply it by the rate

CodePudding user response：

Try below formula-

=SUMPRODUCT(SPLIT(D40,":"),{C41,C41/60,C41/3600})

CodePudding user response：

When you store a value as HH:mm:ss into an Excel sheet, it automatically formats it as a Time, so it makes sense that HOUR modulos by 24.

Which is why you can simply ignore it. If you have a cell that is formatted as currency (FORMAT > Math > Currency) or any other normal Number-like format, then you can see, if you perform a numerical operation like multiplication, that it stores times like "30:30:50" as if it were a TIMEVALUE with a value over 1. Simply multiply that by 24, and then by your hourly rate, and you'll get your value, i.e,

=D40 * C41 * 24 :