Forum Discussion
Anonymous
5 years agoNot applicable
Generate Series from today
I'm trying to create a table with 15 minute appointments from today onwards ( or possibly between two dates if it's easier) I'm using the following to create a table but I'm having trouble filter...
- 5 years ago
Can you post a depiction of the expected result? At the moment the code delivers a table with time values, right?
Edit: see if this works for you:
Appointment Times with date = VAR Opening = 9 VAR Closing = 17 VAR TimeSlot = 30 VAR Offset = ( TimeSlot / 2 ) RETURN GENERATE ( GENERATESERIES ( 0, 1439, 1 ), // 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am) VAR __Minutes = [Value] VAR __Time = TIME ( 0, __Minutes, 0 ) // MROUND: Rounds Value to the nearest multiple of TimeSlot VAR __MinutesRounded = MROUND ( ( __Minutes + Offset ), TimeSlot ) VAR __StartTimeSlotRounded = TIME ( 0, __MinutesRounded - TimeSlot, 0 ) VAR __EndTimeSlotRounded = TIME ( 0, __MinutesRounded, 0 ) VAR TimeValues = ROW ( "Hour", HOUR ( __Time ), "Minute", MINUTE ( __Time ), "Hour Minute 24h", FORMAT ( __Time, "hh:mm" ), "Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ), "30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ), "30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ), "30 Minute Slot", FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-" & FORMAT ( __EndTimeSlotRounded, "hh:mm" ), "Working Hours", IF ( ( __Minutes / 60 ) >= Opening && ( __Minutes / 60 ) <= Closing, "Yes", "No" ), "Time", __Time, "TimeSlot Start", __StartTimeSlotRounded, "TimeSlot End", __EndTimeSlotRounded ) VAR Day2 = TODAY () + 2 VAR Calend = CALENDAR ( TODAY (), Day2 ) RETURN ADDCOLUMNS ( CROSSJOIN ( Calend, TimeValues ), "DateTime", FORMAT ( [Date] & " " & [Hour Minute 24h], "General Date" ), "DateTime Start", FORMAT ( [Date] & " " & [30 Minute Slot Start], "General Date" ), "DateTime End", FORMAT ( [Date] & " " & [30 Minute Slot End], "General Date" ) ) )which gets you this (showing the last columns including the date)
PaulDBrown
5 years agoCommunity Champion
Can you post a depiction of the expected result? At the moment the code delivers a table with time values, right?
Edit: see if this works for you:
Appointment Times with date =
VAR Opening = 9
VAR Closing = 17
VAR TimeSlot = 30
VAR Offset = ( TimeSlot / 2 )
RETURN
GENERATE (
GENERATESERIES ( 0, 1439, 1 ),
// 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am)
VAR __Minutes = [Value]
VAR __Time =
TIME ( 0, __Minutes, 0 ) // MROUND: Rounds Value to the nearest multiple of TimeSlot
VAR __MinutesRounded =
MROUND ( ( __Minutes + Offset ), TimeSlot )
VAR __StartTimeSlotRounded =
TIME ( 0, __MinutesRounded - TimeSlot, 0 )
VAR __EndTimeSlotRounded =
TIME ( 0, __MinutesRounded, 0 )
VAR TimeValues =
ROW (
"Hour", HOUR ( __Time ),
"Minute", MINUTE ( __Time ),
"Hour Minute 24h", FORMAT ( __Time, "hh:mm" ),
"Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ),
"30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ),
"30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"30 Minute Slot",
FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-"
& FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"Working Hours",
IF (
( __Minutes / 60 ) >= Opening
&& ( __Minutes / 60 ) <= Closing,
"Yes",
"No"
),
"Time", __Time,
"TimeSlot Start", __StartTimeSlotRounded,
"TimeSlot End", __EndTimeSlotRounded
)
VAR Day2 =
TODAY () + 2
VAR Calend =
CALENDAR ( TODAY (), Day2 )
RETURN
ADDCOLUMNS (
CROSSJOIN ( Calend, TimeValues ),
"DateTime",
FORMAT ( [Date] & " " & [Hour Minute 24h], "General Date" ),
"DateTime Start",
FORMAT ( [Date] & " " & [30 Minute Slot Start], "General Date" ),
"DateTime End",
FORMAT ( [Date] & " " & [30 Minute Slot End], "General Date" )
)
)
which gets you this (showing the last columns including the date)