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","comment":[{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52513792","author":{"@type":"Person","name":"luv_a_deal"},"datePublished":"2024-04-03T20:14:54.000Z","text":"Hmmm goalposts moving a bit, but this might help with the first bit:
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:
Good luck"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52512582","author":{"@type":"Person","name":"Bob24601"},"datePublished":"2024-04-03T16:39:37.000Z","text":"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)"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52512981","author":{"@type":"Person","name":"nanono8342ikumaru"},"datePublished":"2024-04-03T17:51:00.000Z","text":"Why not use an existing template found online rather than trying to reinvent the wheel?"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52513297","author":{"@type":"Person","name":"jungleboy123"},"datePublished":"2024-04-03T18:47:33.000Z","text":"time to get free Kindle Excel course books!"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52514780","author":{"@type":"Person","name":"Roger_Irrelevant"},"datePublished":"2024-04-03T23:53:44.000Z","text":"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.
etc. "},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52512884","author":{"@type":"Person","name":"luv_a_deal"},"datePublished":"2024-04-03T17:35:21.000Z","text":"How about this:
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"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52512909","author":{"@type":"Person","name":"manowarbruno"},"datePublished":"2024-04-03T17:38:42.000Z","text":"I assume that exactly 8 hours should give 1 hour break? If so you should use the symbol \">=\" (greater than or equal)
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\""},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52513049","author":{"@type":"Person","name":"earthlling"},"datePublished":"2024-04-03T18:00:21.000Z","text":"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)"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52513146","author":{"@type":"Person","name":"TristanDeCoonha"},"datePublished":"2024-04-03T18:19:50.000Z","text":"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"},{"@type":"Comment","@id":"https://www.hotukdeals.com/comments/permalink/52513932","author":{"@type":"Person","name":"earthlling"},"datePublished":"2024-04-03T20:39:08.000Z","text":"I'll give that a go in morning.
Thanks everyone. Really appreciate all the help."}],"commentCount":20,"interactionStatistic":8,"articleSection":"Ask"}