Subtracting time in Excel

To whom it may concern,

I am attempting to build an employee shift schedule in Excel. Ideally, I want a formula that automatically calculates the total hours worked—subtracting them from a target—so that each person lands at exactly 40 hours per week. It works fine for standard shifts, say 2:00 PM to 10:00 PM, but the overnight shifts (10:00 PM to 6:00 AM) are causing issues. I’ll admit 😢, I’m still just scratching the surface with Excel—I can handle the basics, but once formulas get complex beyond simple subtraction, I find myself a bit lost,🙄 and I could use some guidance.
I would be incredibly grateful if someone could provide a straightforward formula to handle this. I’ve already tried using =TEXT(G5-G4,"H") and various TIME functions... but nothing seems to stick. Perhaps I'm just missing something obvious...

Many thanks,

wanderingraven14🙂

There’s a whole lot of info here:
http://www.cpearson.com/excel/datearith.htm

But honestly, what you're actually looking for is this:
=IF(A1>B1;A1+1-B1;B1-A1)

In this scenario, think of A1 as your 10:00 PM and B1 as 6:00 AM.

vividnomad6🙂..thanks, you're a real Knight—ha ha 😉—but according to this formula I'm getting 16. In reality, I should be seeing 8 hours, since anyone working 10 PM to 6 AM is technically just putting in 8 hours, right? Hmm...🙄 is there any way to fix this here? 🙄

Thanks for the response, much appreciated...👍

Wow—thank you ever so much for that link. I actually managed to get a full 8 hours of sleep last night—from 10 PM to 6 AM—which is a minor miracle. I honestly don't know how to thank you 😛... I've been struggling with this for days now... it’s such a relief...👍👍👍👍👍

If I may ask one more thing? 🙄
If I want to take a Personal Day (where they count it as zero)—what’s the protocol there?

Thanks, truly, thanks 🙂

So, if you’ve gone ahead and slapped "SD" into cell B1, then you’ll want to head over to C1 and drop in this formula: =IF(A1="SD";"0";"")
Basically, what I'm getting at is—if A1 shows "SD," just spit out a zero; otherwise, leave it totally blank. Simple enough, right?

Instead of using AT&T in the formula, just swap it out for B1

Dear Nicole Collins6,

Thanks for the reply and all the help.
I was hoping you could clear something up for me:
- I put 10:00 PM in cell A1 and 6:00 AM in B1. Using the formula =IF(A1>B1;B1+1-A1;B1-A1), I got 8:00—which is exactly what I need since that's the actual shift length. Now, I want to be able to type "SD" in the next cell to indicate someone is taking a day off, and have the formula result in 0 if possible. My goal is to just plug in times or "SD" for future schedules and let the formulas handle the math without me touching them again.
Is that actually doable?

Thanks anyway—you've been a huge help...

Maybe try this formula: =IF(A1="SD";0;IF(A1>B1;B1+1-A1;B1-A1))

So, I guess your sheet should be set up so column A has the start time or "SD" if it's a day off, and column B has the end time or just stays blank if it's a day off. Then you can just drag this formula down in column C and hit SUM at the bottom. Kind of looks like this, I suppose:

22:00:00 06:00:00 =IF(A1="SD";0;IF(A1>B1;B1+1-A1;B1-A1))
SD =IF(A2="SD";0;IF(A2>B2;B2+1-A2;B2-A2))
SD =IF(A3="SD";0;IF(A3>B3;B3+1-A3;B3-A3))
8:00:00 17:00:00 =IF(A4="SD";0;IF(A4>B4;B4+1-A4;B4-A4))
7:00:00 15:00:00 =IF(A5="SD";0;IF(A5>B5;B5+1-A5;B5-A5))
=SUM(C1:C5)

EDIT: spacing on this forum is kind of weird, so sorry if it looks messy😠 All those "IF"s are actually on the same line😉

wanderingraven14 said:Hey Nicole Collins6, I want to put "SD" in the next cell to show someone's off, and then have the same formula calculate it as 0 if possible.

Try this: =IF(A1="SD","0",B1+1-A1)

But honestly, just a heads up
- What about Sick Leave - BO - 8 hours?
- What about Vacation - GO - 7 hours?

If you wanna account for those too, try this one
=IF(A1="SD","0",IF(A1="BO","7",IF(A1="GO","8",B1+1-A1)))

[edit]: rapidranger61 beat me to it ;-)
btw: no clue why I didn't see his post in my browser, especially since there was like a 30-minute gap between posts?

Kyle Johnson2 said:[edit]: rapidranger61 beat me to it ;-)
idk why his post didn't show up in my browser... maybe because there was like a 30-minute gap?

Come on, man... 😁

Good grief, everyone... you all are truly wonderful.👍 I mean it—I adore this group. Cheers to everyone... really.😉 rapidranger61—you’re a total powerhouse—and thanks to Kyle Johnson2 too... you guys are incredible, honestly don't know how to thank you properly...

👍👍👍

THANK YOU 🙂

Listen, I have one final question regarding this schedule of mine... I’m managing a crew of 20 people, and the layout spans across two different pages. When I start entering dates into the cells on the first page, is there any way to make them automatically populate the corresponding cells on the second page?
Is there some kind of formula for this sort of thing?
Thanks

Give this formula a shot in cell C: =IF(AND(A1;B1>0);IF(A1>B1;B1+1-A1;B1-A1);"SD"). Here’s how it breaks down: if you’ve got timestamps entered in cells A1 and B1, cell C1 will calculate the difference (like that 8-hour gap in your example). But, if there’s nothing entered in those cells—which wouldn't happen if the employee actually clocked in—then cell C just displays "SD" for sick leave. I hope I caught your drift here, and honestly, even more so if this actually solves your headache.

I honestly can't thank you all enough—it’s been a massive help...
Does anyone have an idea or maybe a specific formula regarding dates—specifically for that post I put up earlier? 🙄

wanderingraven14 said:Oh my god guys... you are seriously the BEST!!!👍 Love you all!!! lol... here's a drink for everyone... hehe...😉 rapidranger61—you're basically a superhero... and thanks to Kyle Johnson2 too... you guys are awesome, I don't even know how to thank you...

No biggie, we'll just send you the bill later 😁

As for that other issue, that can be fixed too 😉

Say you want a date from cell A1 on the first sheet to automatically show up in A1 on sheet 2. You'd just go to that second sheet, hit cell A1, and type this formula:
=Sheet1!A1

Just gotta make sure that first sheet is actually named "Sheet1". If it's called something else, just update the formula to match.

Look, if you're trying to sync up dates across two different sheets—say you type a date into cell A2 and you want that exact same date to pop up over on Sheet1 in cell A39 without typing it twice—there’s a simple fix. Just head over to cell A39 and drop in this formula: =A2. That's it. Once you hit enter, whatever you put in A2 will automatically mirror itself in A39. It saves a bit of clicking, I suppose. Also, just a quick heads-up regarding my previous point: if having "SD" sitting in your table is messing with your math functions or causing errors, just tuck a "0" into your formula instead. It keeps the numbers clean.

To all you brilliant minds out there... if it weren't for this community, I'd probably be spinning my wheels in circles for years to come. You've done a real service here. 🙏

rapidranger61... I'm actually proud of you. 🙂

I tried putting together a formula based on what Kyle Johnson2 suggested—he was covering all the bases (BO, GO... and so on)—but now I've hit a snag with the SUM of all the cells. For instance, I put the start time in A1 and the end time in B2, then dropped the formula into A3 and B3 after using Merge and Center on those two cells. I did that for the whole set... and now the SUM is acting up. Is that just bad luck... or have I messed up again? 😕

Testing out some dates now... wish me luck. 😉

Maybe it's just the cell format for the SUM. If it's set to a standard time format, it probably resets after 24 hours. So if someone worked, I don't know, 30 hours, it won't show up right. Maybe try going into Format Cells and picking that "37:30:55" option from the list. See if that fixes it 😉

Nicole Collins6! Yes... the date actually worked... I’m absolutely crushing this... haha 😛...
What am I supposed to do with the SUM cells? It just won't calculate the final total for me. 😢

This schedule is going to be the death of me... ☕ all these sleepless nights because of this thing...

God Pink Sheep 🙏🙏🙏 I tip my hat to you—do you actually follow any specific guidelines based on this? Because if you do... I’d be more than happy to become your student. Much love 👍