Posted 2 days ago

Excel formula help

This might be really simple for someone but the last time I did excel was probably 20 years ago, so really appreciate any help.

I am trying to create a rota on excel.

Cell A: start time
Cell B: end time
Cell C: B1-A1 to work out the hours of the shift.
Now I'd like an IF statement which works out the break and takes away from the hours worked...ie If C>8 then take away 1 hour if <8 then take away 30 minutes. The hours after the breaks taken away need to be displayed in Cell C1
I don't want an extra cell to be used so all this info needs to be in Cell C1.

Thanks in advance
Community Updates
New Comment

20 Comments

sorted by
's avatar
  1. luv_a_deal's avatar
    Hmmm goalposts moving a bit, but this might help with the first bit:

    =IF(B1>0,IF(B1-A1>=TIME(8,0,0),B1-A1-TIME(1,0,0),B1-A1-TIME(0,30,0)),0)

    If you want to add a (running) total, the time format potentially causes a problem, as in reality it's held as date/time and is in effect capped at 24 hours (from a display point of view at least). Could perhaps enhance the above to get number of minutes that you can total better (need to format cells to number), for example:

    =IF(B1>0,(24*60)*IF(B1-A1>=TIME(8,0,0),B1-A1-TIME(1,0,0),B1-A1-TIME(0,30,0)),0)

    to then manipulate as you require.

    Good luck
  2. Bob24601's avatar
    It's been a while for me too, and not in front of my computer either.

    But it be something like this in C.

    If((B1-A1) >8,B1-A1-1,B1-A1-0.5) (edited)
    earthlling's avatar
    Author
    If I format the cells to "Time" it doesn't seem to work. For example I had 08:00 to 17:00 but the result came to 21:00.

    But once set to "Number" it's working.
    Thanks for your help.

    Just realised I can't work with half hours with Number format.. :/ (edited)
  3. nanono8342ikumaru's avatar
    Why not use an existing template found online rather than trying to reinvent the wheel?
    MadeDixonsCry's avatar
    Because hukd is the new chatgpt
  4. jungleboy123's avatar
    time to get free Kindle Excel course books!
  5. Roger_Irrelevant's avatar
    Looking at all the above makes me weep but also laugh a little.

    There's been these things called "Databases" for like 50 years, which means expensive "Cottage Industries" of Excel experts are a thing of the past.

    Except the one on every single episode of "The Apprentice" apparently.

    52514780-Yn80u.jpg
    52514780-a0b0I.jpg
    etc.
    HellRazer's avatar
    If you used Excel as a database, more the fool you... Next you'll be telling me MS Access was the first time you used a database. (edited)
  6. luv_a_deal's avatar
    How about this:

    =IF(B1-A1>TIME(8,0,0),B1-A1-TIME(1,0,0),B1-A1-TIME(0,30,0))

    You might need to finesse (eg if you want exactly 8 hours to take an hour, put an "=" after the ">"), but hopefully puts you on the right path
  7. manowarbruno's avatar
    I assume that exactly 8 hours should give 1 hour break? If so you should use the symbol ">=" (greater than or equal)

    The equation is:


    =IF((B2-A2)>=(1/24*8),(B2-A2)-(1/24*1),(B2-A2)-1/24*0.5)

    or the same outcome :

    =IF((B2-A2)>=0.333333333333333,(B2-A2)-0.0416666666666667,(B2-A2)-0.0208333333333333)

    It will return a numerical value but you need to format it as "time" to display correctly. The key for Excel time format is that "1 = 1 day = 24 hours"
  8. earthlling's avatar
    Author
    The above 2 work fine with using the Time format.
    Is there any reason why if B2 and A2 are both empty C2 shows 23:30, it changes the total hours cell (I'd like it add each days hours or even better to count down from let's say 40hrs to show what's left after inputting each shift)
  9. TristanDeCoonha's avatar
    On a 7 hour working day, is it necessary to give an hour? You are obliged to put a 20 min break in there, so why not make it 30 min and let folks go home 30 min early, or come in 30 min late? Then you could easily work the shift out so that there is cover at all times, and people don't feel bad that an hour of their day is just wasted.
    Unless you are like my last employer who gave an hour unpaid lunch, but knew that sitting in the can looking at the site would bore you to tears after 30 min that you had to do something for your sanity
    earthlling's avatar
    Author
    It's 30 mins for anything less than 8 hours, that's exactly what I've done.
    8 or more hours an hour break anything less 30 mins break
  10. earthlling's avatar
    Author
    I'll give that a go in morning.

    Thanks everyone. Really appreciate all the help.
's avatar