I'd like to create a spreadsheet where I can calculate when a time period will end by adding a certain number of time periods to a base time. For instance, The base time (starting time)I wish to use is 9:30 a.m. I wish to add 15 minute time periods to the base time. For this example, I'll randomly choose 43 time periods.
There are 27 15 minute periods in the day. (Starting at 9:30 a.m and ending at 4:15 p.m.) If I have cell A1 Filled with the 9:30 a.m. starting time and cell B1 filled with the integer 43 how could I compute the ending time in cell C1?
Let's say the day I need to start from Tuesday, so the starting time is 9:30 A.M. on a Tuesday.
I don't wish to include weekend days and I don't wish to include time periods outside the 9:30 a.m. to 4:15 p.m. time frame.
I can do the math on paper seeing the 43rd period will end at 1:30 p.m. Thursday. (43 periods minus 27 periods per day = 16 periods. 16 15 minute periods is 4 hours. 4 hours from 9:30 a.m. is 1:30 p.m.)
Anybody willing to help me code this?
Best Answer
What you need is the modulo operation, aka the remainder.
Divide the total number of periods (43) by the number of periods in a day (27). The whole part of the quotient (1) is the number of whole days to add. The remainder (16) is the number of periods to add to the time.
Now just know that Sheets (and Excel) store datetimes as numbers.
One day = 1. Just add the number of whole days to your starting datetime.
Times are fractions of a day, so 15 minutes =
15 min ÷ 60 min/hr ÷ 24 hr/day= 0.010416... days. Multiply that by the number of additional periods (the remainder) and add to the datetime.Make sure you format your datetime cells as such, and voila:
Paste this into Sheets to try it.
Skipping weekends is a whole other level of difficulty. You'll need to check each day with
WEEKDAY()and skip it if it's a weekend. At that point, you might be better off writing a script.