Wrong Value Returned When Using A Calculated Time

[Josh_Osowiecki]

Hello All!

I am attempting to create a simple timesheet that will automate processes to save some time. I am running into an issue when I multiply my rate column by my hour column. To calculate total hours I am using the below equation:

=IF([@[End Time]]>[@[Start Time]],[@[End Time]]-[@[Start Time]],1-[@[Start Time]]+[@[End Time]])

This provides a value for a time range in an integer from 0-1. I adjusted to format to h:mm to show the total hours. For example, a record with a Start time of 7 am and an End time of 5 pm will display as '10.00' in a separate field labeled 'Total Hours'. The rate being used in this example is $20/hour

I am running into an issue when I multiply the rate field by the corresponding total hours field. For the example above excel is returning  $8.33 instead of $200.

I know this is because Excel is giving me the solution to 0.416(of a day)*$20. I would like to know how to make Excel instead provide the solution to 10 hours*$20.

Any help would be greatly appreciated!

[#2 · Accepted solution]

@Josh_Osowiecki 

One option is to make the hours formula return decimal hours:

=24*MOD([@[End Time]]-[@[Start Time]],1)

Format the cells with this formula as Number or as General.

Another option is to leave the hours column as time, and to multiply the end result with 24:

=24*[@[Total Hours]]*20

[#3]

Thank you! I tried both out and both worked great! very much appreciated!