My question is similar to this one in that I'm struggling with Excel's Mod formula. Mostly I just wish for clarification on how Mod function works in relation to time and if I'm making this harder then it has to be.
The situation is this, I'm made a spread sheet showing the time difference between several time zones and their coordinating times for a online game. I have an assumed wake time: 6am to 9:pm. To see which time zones would be awake for the 12+from whatever 'start time' the game starts, I made a separate chart with those times with the formula =time+12/24 (I'll refer to this as the prep time chart.) (The 48+hours from that same 'start time' just ends up being the same start time so I didn't bother making a chart for this.)
Later when I try to have a sum total column for each time showing who would be 'awake' using the formula =COUNTIFS(B3:B9, ">=" 6:00 AM, B3:B9, "<=" 9:00 PM)(don't worry the 6am and 9pm are stand-in for you guys, they actually ref cells that hold those values). This works perfect for the 'start time' chart but the count ends up being off on the prep time' chart. I also came across errors on other formulas that build off these two charts and that COUNTIF formula that I wont mention as I believe I've isolated the problem to my understanding on how the MOD function works.
When just examining one time zone (ex. central time zone) using the same countif formula the count of awake from the 'start time' chart is correct but the count of awake for 12+ using the 'prep time' chart is correct until it hits the 12:00 am mark. When fooling with it the opposite happens when I change the 'prep time' chart formula to =time-12/24. But doing this also falls out of my formatting condition statements. I'd really like to not go in and change for every row/time zone the formula starting at that 12am marker or the COUNTIF formula as the sheet is suppose to be almost fully automated after adding/subtracting time zones.
I've given myself a real headache trying to adapt the COUNTIF statement to account for the all the different situations of 6am-9pm, 9pm-12pm, 1am-6am.
The similar question I reference explains that the error happens because the cell format is set to a date and time setting but I've checked and all my times cell's formats are set to time type 1:30 pm. And yeah, the format has am/pm but who can read time in decimal format anyway? Not me.
I don't know how to attach the sheet so just to give a visual- columns are times (3:00pm, 4:00pm, 5:00pm etc) and rows are the different time zones I'm comparing (ex. central time zone (ctz), japan (JST), Lebanon (EET), etc)
If someone can solve my excel glitch that's super but I'd settle for a detailed explanation of how the MOD formula behaves in relation to crossing that new day time.
I've finished the problem manually but I'm curious how to get excel to give me the answer, so no rush!
I've adapted everything I'm doing with the MOD function from this site.
