Calculate time intervals based on start and end time

FireBott

Board Regular
Joined
Nov 10, 2003
Messages
65
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I am developing a spreadsheet for my group to administer the vaccine. Each group will have a different start time, end time and duration of the appointment (this could be 5 min, 10 mins, 15 mins). How do you calculate the increments of time based on the start time, end time and duration with the formula stopping at the end time that is entered

I need the user to enter in the start time and the end time and the duration and have the formula calculate all of the intervals up to the end time entered. I figured out the formula to calculate the intervals, is there away to write it to stop calculating when it hits the end time. Current Formula is =B6+TIME(0,$E$2,0). The reason I need it to stop at the end time is I have to a count of the number of increment slots in a separate field.
MrExcel Help Time.png



Any help would be appreciated
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,791
10 in excel is 10 days so divide by 24 to get hrs and by 60 to get mins.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365
+Fluff 1.xlsm
ABCDE
1
209:3011:0010
3
4
5
609:30:00
709:40:00
809:50:00
910:00:00
1010:10:00
1110:20:00
1210:30:00
1310:40:00
1410:50:00
1511:00:00
16
Master
Cell Formulas
RangeFormula
B6:B15B6=SEQUENCE((C2-B2+TIME(,10,))/TIME(,10,),,B2,TIME(,E2,))
Dynamic array formulas.
 

FireBott

Board Regular
Joined
Nov 10, 2003
Messages
65
Office Version
  1. 2016
Platform
  1. Windows
Thank you @Fluff. I just updated that information. I am on 2016. The sequence formula did not work. Thank you
 

FireBott

Board Regular
Joined
Nov 10, 2003
Messages
65
Office Version
  1. 2016
Platform
  1. Windows
I can't thank you enough. That worked. This will save me at least 3 hours per day during these vaccine times. You are the best
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top