Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to use row number in formula/how to extract specific value from other cell (excel)

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

one cell

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

two cells

I’m thinking two ways I could achieve this are:

  1. write the formula to get the times of the previous cell and add fifteen minutes to both times
  2. 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.

>Solution :

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")
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading