Wrong Value Returned When Using A Calculated Time

New Contributor

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)


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

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