Time Formula with a defined end

manny1975

New Member
Joined
Apr 8, 2009
Messages
33
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,856
Office Version
  1. 2016
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:
Code:
=MIN(C2+1/48,CHOOSE(WEEKDAY(A2),9,17,14,17,17,17,17)/24)

Book1
ABCD
1DateWeekdaytimeend
204-AprMon17:0017:00
305-AprTue14:0014:00
406-AprWed09:0009:30
507-AprThu09:0009:30
608-AprFri09:0009:30
709-AprSat09:0009:30
810-AprSun09:0009:00
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=A2
A3:A8A3=A2+1
D2:D8D2=MIN(C2+1/48,CHOOSE(WEEKDAY(A2),9,17,14,17,17,17,17)/24)
 

Forum statistics

Threads
1,176,598
Messages
5,903,961
Members
435,061
Latest member
CGA

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