Viewing 14 posts - 1 through 14 (of 14 total)
  • Excel – using time as a multiplier
  • geetee1972
    Free Member

    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?

    ojom
    Free Member

    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?

    slackboy
    Full Member

    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.

    ojom
    Free Member

    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.

    burko73
    Full Member

    I think I had this problem. I’ll post when I get 5 mins in work.

    geetee1972
    Free Member

    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?

    Sandwich
    Full Member

    Format the sum cell with a custom format [h]:mm and jobs a good ‘un. I had the same problem on my timesheet.

    enmac
    Free Member

    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.

    HughStew
    Full Member

    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.

    Sandwich
    Full Member

    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.

    geetee1972
    Free Member

    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.

    HughStew
    Full Member

    the [h] bit is the important

    Ooo, so it is. That’s useful.

    Sandwich
    Full Member

    It took me a little time to find, Google as usual was more helpful than Excel when searching for the solution.

    Sundayjumper
    Full Member

    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 negative 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.

Viewing 14 posts - 1 through 14 (of 14 total)

The topic ‘Excel – using time as a multiplier’ is closed to new replies.