+ Reply to Thread
Results 1 to 6 of 6

How can I tell excel to recognise 0.5 as 30 minutes?

  1. #1
    Registered User
    Join Date
    05-30-2009
    Location
    leeds
    MS-Off Ver
    Excel 2007
    Posts
    6

    How can I tell excel to recognise 0.5 as 30 minutes?

    Please see my image attachment.

    I want paid hours to be a sum of:
    total hours - break = paid hours

    Whenever I try to set the break column as a number of minutes, it recognises it as the time instead.

    Also, how can I tell excel to recognise that the numbers in the 'total hours' box as hours and not the time.

    Thanks, Sam.
    Attached Images Attached Images

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How can I tell excel to recognise 0.5 as 30 minutes?

    Divide by 24,ie

    H2: =F2-(G2/24)
    formatted to hh:mm

    (it would be easier no enter breaks in time format, ie use 00:30 in G2 as opposed to 0.5 - then F2-G2, no ?)

  3. #3
    Registered User
    Join Date
    05-30-2009
    Location
    leeds
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How can I tell excel to recognise 0.5 as 30 minutes?

    you're right, thanks a lot that's very helpful, i've only ever used excel a handful of times.

    i don't know if this is too much to ask but would you be able to work out a allocated break time formula using these rules?

    4:00 or less = no break
    4:01 - 6:30 = 30 mins
    6:31 - 8:00 = 1 hour
    8:01 - 10:00 = 1 hour 30 mins
    10:01 - 12:00 = 2 hours

    you cannot work more than 12 hours.

    would be greatly appreciated if you can! thanks!

    Quote Originally Posted by DonkeyOte View Post
    Divide by 24,ie

    H2: =F2-(G2/24)
    formatted to hh:mm

    (it would be easier no enter breaks in time format, ie use 00:30 in G2 as opposed to 0.5 - then F2-G2, no ?)

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How can I tell excel to recognise 0.5 as 30 minutes?

    One way to do that would be to use this formula in G2 copied down

    =IF(F2*24>10,2,IF(F2*24>8,1.5,IF(F2*24>6.5,1,IF(F2*24>4,0.5,0))))

  5. #5
    Registered User
    Join Date
    05-30-2009
    Location
    leeds
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How can I tell excel to recognise 0.5 as 30 minutes?

    See my attached file.

    I've made a few changes to my spreadsheet, however with my lack of excel knowledge it's not going to plan!

    Total hours, break and paid hours are not totalling up correctly. Neither is the money earned section for each day, however the weekly total is working fine.

    Thank you for your formula daddylonglegs, but I can't seem to get it working, but maybe that's due to the few changes I've made to my spreadsheet since my first post.

    So again, if anyone can write a formula to work out the allocated break time using the following rules, then I'd be very greatful.

    4:00 or less = no break
    4:01 - 6:30 = 30 mins
    6:31 - 8:00 = 1 hour
    8:01 - 10:00 = 1 hour 30 mins
    10:01 - 12:00 = 2 hours
    You cannot work more than 12 hours.

    Thanks a lot!
    Attached Files Attached Files

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How can I tell excel to recognise 0.5 as 30 minutes?

    If you want the break shown in time format you can use the formula I suggested but divided by 24, i.e. in G2

    =IF(F2*24>10,2,IF(F2*24>8,1.5,IF(F2*24>6.5,1,IF(F2*24>4,0.5,0))))/24

    then H2 would be just

    =F2-G2

    and J2 for total pay

    =H2*I2*24

    hours total at the bottom need to be formatted as [h]:mm so that hours totals over 24 hours are correctly displayed, see attached
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1