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

1. ## 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.  Register To Reply

2. ## 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 ?)  Register To Reply

3. ## 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!  Originally Posted by DonkeyOte 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 ?)  Register To Reply

4. ## 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))))  Register To Reply

5. ## 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!  Register To Reply

6. ## 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  Register To Reply

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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