Posts by wanderingraven14

I finally figured out why my totals were all messed up—turns out I was using that formula from Kyle Johnson2, the one that covers everything (BO, GO...) `=IF(A1="SD";"0";IF(A1="BO";"7";IF(A1="GO";"8 ";B1+1-A1)))`. The problem is, it was calculating the hours between 2:00 PM and 10:00 PM as 32 working hours... which explains the massive number I kept seeing at the end of my SUM. I guess it’s because there wasn't a specific check for (A1>B1). I've tweaked the formula now and it's actually working correctly...but now I've lost the GO & BO functionality...oh well. I suppose this is it—the schedule is finished, and honestly, it looks STUNNING thanks to all of you...🙏

THANK YOU ALL SO MUCH...EVERYONE!

rapidranger61... I tried formatting an entire column where my SUM was set to "37:30:55"—and now, for someone who supposedly worked 24 hours this week—it’s telling me they clocked 56:00:00... great... 😢😢😢

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 👍

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...

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. 😉

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? 🙄

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

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 🙂

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...

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 🙂

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...👍

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🙂