Time Formula with a defined end

[manny1975]

Hi all,

Trying to help my wife with something for work. I created a data validation where a doctor she works for has hours between 9-5 in half hour increments. What I am trying to accomplish is in the drop down of the data validation she picks 11:00 am and the end time is 5:00 pm for Monday, How can the cells underneath this add the increments of 30 min (11:30, 12:00PM, 12:30 PM) up to the end time of 5:00 pm? But lets say the Tuesday she picks (9:00 am to 2:00 pm, the increments will occur but end at 2:00 pm? Same with the 11-5 it ends at 5....this way she can just plug in patients names those 30 increments.

Thank you!

 

[bebo021999]

With A2 is date, C2 is time
assum from sunday to saturday, end time is 9h,17h,14h,17h,17h,17h,17h (sunday is off, tuesday is 14:00)
in D2:

=MIN(C2+1/48,CHOOSE(WEEKDAY(A2),9,17,14,17,17,17,17)/24)

Book1
	A	B	C	D
1	Date	Weekday	time	end
2	04-Apr	Mon	17:00	17:00
3	05-Apr	Tue	14:00	14:00
4	06-Apr	Wed	09:00	09:30
5	07-Apr	Thu	09:00	09:30
6	08-Apr	Fri	09:00	09:30
7	09-Apr	Sat	09:00	09:30
8	10-Apr	Sun	09:00	09:00
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=A2
A3:A8	A3	=A2+1
D2:D8	D2	=MIN(C2+1/48,CHOOSE(WEEKDAY(A2),9,17,14,17,17,17,17)/24)