I have this formula in my first cell
=TEXT(TIME(3,0,0), "H:MM AM/PM") & " to " & TEXT(TIME(3,15,0), "H:MM AM/PM")
It displays
I want to flash fill or create a formula that increases both times by 15 minutes as it goes down the column so it will look like this
I'm thinking two ways I could achieve this are:
- write the formula to get the times of the previous cell and add fifteen minutes to both times
- write a formula to get the number of the row and add 15 *that number to both times
I, however, don't know how to do either. Would appreciate help.
CodePudding user response：
To add 15 minutes every row, do ROW()*15. and since it starts from the first row it should be (ROW()-1)*15.
=TEXT(TIME(3,0 (ROW()-1)*15,0), "H:MM AM/PM") & " to " & TEXT(TIME(3,15 (ROW()-1)*15,0), "H:MM AM/PM")
CodePudding user response：
This is certainly doable with the first method; I've never used the row number directly in the formula.
If you have spare columns, you can use these to generate the numbers.
Cell A1 reads 3 and Cell A2 reads =IF(B2=0,A1 1,A1)
Cell B1 reads 0 and Cell B2 reads =IF(B1 15=60,0,B1 15)
In both of those forumale, I'm using the if to decide when to iterate to the next hour, and jump back to zero minutes. It would also be possible to construct this using modular division, for example.
Once you have the numbers, your formula is easily adaptable:
=TEXT(TIME(A1,B1,0), "H:MM AM/PM") & " to " & TEXT(TIME(A2,B2,0), "H:MM AM/PM")
All of the formulae, except for cells A1 and B1 where I set the starting time, could be flash filled.
It looks like the TIME() function automatically handles numbers larger than 24h, too (i.e. time(26,0,0)=2:00am).
CodePudding user response：
For Office 365:
=TEXT(SEQUENCE(96,,0,15)/1440,"H:MM AM/PM")&" to "&TEXT(SEQUENCE(96,,15,15)/1440,"H:MM AM/PM")
Adjust the first parameter in both instances of the SEQUENCE function (96 here) to determine how many results to return. Currently the above will return 96 rows' worth of data beginning with "12:00 AM to 12:15 AM" and ending with "11:45 PM to 12:00 AM".