SOLVED

# Wrong Value Returned When Using A Calculated Time

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 Replies
best response confirmed by Josh_Osowiecki (New Contributor)
Solution

# Re: Wrong Value Returned When Using A Calculated Time

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

# Re: Wrong Value Returned When Using A Calculated Time

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