Forum Discussion
Time values
I want to add a column of times (h:mm values) not dates or daily times, and then work with these values to calculate average duration of hours spent on various tasks.
I have tried using the TIME function and TIMEVALUE function to format the total time spent on a task but can't seem to get any values which actually work.
Can anyone assist?
Craftybilda In B41, you see 02:45, being 2 hours and 45 minutes. But dates and times in Excel are in fact numbers (a full day is being 1, and for instance 12 hours equals 0.5 etc.) In this particular case, the underlying value is 0.114583333333333. Your formula in B42 creates a time value of 0 hours, 0 minutes (the leftmost number) and 33 seconds (the two rightmost numbers). What exactly do you want to show in B42 and B43? In case you want to "transform" 02:45 to decimal hours (i.e. 2.75), you can multiply the B42 by 24 and format the cell as a number.
4 Replies
At present your 'Total hours of palletising' shows a time duration of 02:45 but the value that gives rise to the display is actually 0.114583333 as a part of a day. I don't think you really need to convert to hours at this stage but, if you wish to, simply multiply B41 by 12.
If you do not convert from days to hours at this stage, your result will be measured in Pallets/Day. Converting the figures at that stage requires division by 24 to give Pallets/Hour.
Hopefully, this will simplify your intended calculation.
- I could not understand what you want.
I mean, your calculated value is showing there the total of hours. It means the amount of hours is already calculated. 02:45 on B41.
If you adjust the formula to =TIME(HOUR(B41),MINUTE(B41),SECOND(B41)) it will have the same result 02:45.
Ps.: Its not part of the problem, but you can also convert to decimals by multiplying to 24. Craftybilda In B41, you see 02:45, being 2 hours and 45 minutes. But dates and times in Excel are in fact numbers (a full day is being 1, and for instance 12 hours equals 0.5 etc.) In this particular case, the underlying value is 0.114583333333333. Your formula in B42 creates a time value of 0 hours, 0 minutes (the leftmost number) and 33 seconds (the two rightmost numbers). What exactly do you want to show in B42 and B43? In case you want to "transform" 02:45 to decimal hours (i.e. 2.75), you can multiply the B42 by 24 and format the cell as a number.
- Many thanks indeed Riny,
I have solved the problem with your assistance. I simply had to reformat B41 (02:45) as text, then sum the these time values, and calculate the number of palates handled per hour. The main problem was to have the only exercise for the handlers to do was to enter the hours worked in handling the palates, the excel calculates the number of palates handled per hour automatically. Management can then look at methods of maximizing efficiency.
