Forum Discussion
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!
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
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!
