Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
My don't I live in the fast lane; 7.20am and asking questions about Excel formula!
I have a sheet that records hours worked for an employee. The cells that record the time are formatted as hh:mm and they total at the bottom where the total should then indicate the number of hours worked.
For some reason, formatting the cells like this doesn't result in the correct sum being returned. I have four cells showing "07:30" and yet the total shows "06:00" when it should show "30:00", i.e. 30 hours.
What am I doing wrong?
You need to just use number format. i.e 7.5 hours worked. The time formatting makes excel think it is a time of day i.e. 0730 am, not 7.5 hours worked.
Does that help?
time format only shows hours not day and hours e.g. 24 hours shows as 00:00:00 as does 48 hours. not helpful.
what I do insert another column and multiply the time column by 24 to give the hours in decimal format. e.g. 6:30 becomes 6.5 hours.
or you could just do that to total cell get the answer you need.
To elaborate, at work, we run a timesheet, the cells for each day are calculated by (each cell)
'=17.5-0.5-8.75'
That gives you the hours worked for the day.
These are summed at the end of the row/columns and hey presto, the hours work.
I think I had this problem. I'll post when I get 5 mins in work.
Does that help?
yes it does, thanks, but it then throws up another small issue. Without getting too technical and boring, the 'hours' column is recording how many hours are available to work that day/week (which is the total i was referring to).
Next to it I have start time and then finish time using a time format, i.e. start= 7:00 finish=17:00, then I have a cell that works out how many hours that is for the day total=10:00. This last column then adds up the actual hours worked in a week and then the month and then shows me how many hours overtime are owed.
If I have one cell formatted just as a number for total available hours and another which is formatted as time showing actual hours, how do I structure a subtraction formula so that it shows the difference in hours?
Format the sum cell with a custom format [h]:mm and jobs a good 'un. I had the same problem on my timesheet.
Not sure I fully understand your question, but irrespective of how the number is formatted, Excel stores time in decimal days so to get hours just subtract one from the other and multiply by 24.
Format the sum cell with a custom format [h]:mm and jobs a good 'un. I had the same problem on my timesheet.
Think this will only work if the total is less than 24hrs, as h:mm format will not display days.
Format the total cell as number and multiply it by 24, as a datetime is stored as single number in days, so 8 hrs is .3333...
Might depend on Excel version - but I doubt it.
It works for my time sheet for 100 plus hours. the [h] bit is the important part, normally it uses hh:mm which resolves to 24 hour times only. Excel 14 for Mac.
the [h] bit is the important part. Excel 14 for Mac.
It would appear to be the case on my sheet as well. All working now. Combination of using this format and dividing the time total by 24.
Excellent support as ever guys. Many thanks for taking the time to respond.
the [h] bit is the important
Ooo, so it is. That's useful.
It took me a little time to find, Google as usual was more helpful than Excel when searching for the solution.
I see this has already been answered but I'll chuck in this tidbit because it took me a while to figure out and it might help someone - if you ever need to work with [i]negative[/i] times you need to switch to the 1904 date system. It's tucked away in one of the options menus.
I needed this when working out some results for a race where riders were ranked according to their performance against a target time, i.e. their result needed to be expressed as ± their target. Excel was throwing up an error for anyone that went under their target time.