Subtracting time in Excel

wanderingraven14 said:God Pink Sheep 🙏🙏🙏 I'm obsessed... do you actually follow some kind of guide for this? because if you do... I'd happily be your student... love yaaa 👍

Yeah 😁

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

So, if you're dealing with numbers formatted as times—say, something like 8:00—and you actually need them to act like regular integers, here’s a little trick. You can basically force them to become just "8" by going into the Custom formatting settings and typing > into the Type field. Once you've done that, just select all those cells where the times are displayed, hit that > format, and boom: you've got simple numbers that won't give you a headache when you try to run a >. Good luck with it.

wanderingraven14 said:rapidranger61... I tried formatting the whole column where my SUM shows "37:30:55" and now, say, for someone working 24 hours that week, it's showing 56:00:00... ugh...😢😢😢

Hmm, I don't know what the issue is, looks fine to me!
Maybe try it the way Nicole Collins6 would do it, should work 😉

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!

wanderingraven14 said:Hi everyone,

I'm trying to set up a staff schedule in Excel. Ideally, I want to input the shift times and have a formula automatically calculate the hours worked so I can track when someone hits their 40-hour week. It works fine for standard shifts (like 2:00 PM to 10:00 PM), but I'm hitting a wall whenever there's an overnight shift (like 10:00 PM to 6:00 AM). I'll admit 😢, I'm still pretty new to Excel and mostly just stick to the basics. Formulas beyond simple subtraction like (=G4-G5) have me a bit stumped,🙄 so I could really use some help.
I would be so grateful if someone could point me toward the easiest formula to handle this. I tried using =TEXT(G5-G4;"H") and playing around with the TIME function... but nothing is working (or maybe I'm just doing it wrong).

Thanks so much,

ammod🙂

Just try this: =MOD(A1-B1,1)