Discussions

Expand all | Collapse all

Formula for Calendar Time Block (Start Time + Duration)

  • 1.  Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-03-2019 17:49
    I have what I think is a simple question, but I cannot create a working formula.
    I need to have blocked off times for individuals on a calendar.
    I have an event start time for individuals in a form with two separate date and time fields. I have successfully combined them into a single Date & Time field using the Date/Time Formula:
    ToTimestamp([Date of Event], [Time of Event]) (2:30pm on 6/5/2019 for instance)

    But I also have on the form a Duration field for the event (so 2.5 hours for instance).

    But now I need to have my calendar show that time blocked off for the full duration, from the start time of 2:30 until 5pm.
    But when I try to add it in, it gives me syntax errors, and I have tried several different approaches.

    The one that seems to be the closest is:
    ToTimestamp([Date of Event], [Time of Event]) + [Duration of Event Formula]
    "Duration of Event Formula" = [Total Event Duration] from the form as type Duration.
    But that only seems to move my start time on the calendar 2.5 hours ahead from 2:30pm so it shows a start time of 5pm.

    How do I get this formula to work so that I can keep my form with the separate Date & Time fields, and incorporate the duration of the event to show up on my calendar correctly?





  • 2.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-03-2019 19:12
    Try wrapping your duration in Hours(). You might also have to call ToTimeOfDay on the time field, depends what type of field it is.

    So something like this, as long as the duration is numeric. Might have to play around with it a bit but this should do it. I was getting some weird highlighting in QuickBase on the word Duration so you might shorten that or change it. Might not matter.

    ToTimestamp([Date of Event], [Time of Event])  + Hours([Duration of Event Formula])

    I'm not sure how it behaves when you feed it something like 2.5 instead of 1 or 2 so it might actually fail.


  • 3.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-03-2019 21:23
    Thanks Austin. That got me halfway there. So here's what I now see.
    No yellow on the formula, so that's a plus. But if I have an 11AM appt for instance with a total duration of 3 hours, then the calendar shows the start time of 2pm and end of 2pm. Obviously not what I need it to show.
    (the 11:00am you see on the 9th next to the name is part of the "Each Day Displays" under Customize this report)
    I still want to show the calendar time blocked off for the time and full duration of the appointment.
    I think what I need is some sort of joiner to get it to show the Start time [Time of Appointment], and then calculate that the end time is the start time plus the duration [Duration Formula].
    Hoping this is possible.

    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1791868/RackMultipart20190603-62149-1kh9lo7-Error2_inline.PNG?1559596482">


  • 4.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-03-2019 22:44
    Eli,
    Can you tell me your formula for your end date/time?  It seems to me from your post that you made a formula for the start date/time when you wanted to make a formula for the end date/time.


  • 5.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-04-2019 14:36
    Hi Coach,

    Please forgive me, I am new to Quickbase and formulas, so I appreciate your help. Right now it's a lot of trial and error.

    I don't have a set 'end time' on my form. I use a duration on my form.

    I have a beginning time of the appointment, and an entry for the duration of the appointment. And I need the calendar to block that time for the client from beginning to end of the appointment.


    This is my duration field that is on the form. It is numeric.



    This is my Duration Formula, which is just pulling the Total Duration (so I guess it's basically redundant)
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1791957/RackMultipart20190604-28167-vu0s9q-Error5_inline.PNG?1559657067">


    And this is my formula I'm trying to use to block of the time on the calendar.
    The start time is a formula combining the Date of Appointment field from my form and the separate Time of Appointment field. Hope that makes sense...
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1791958/RackMultipart20190604-80090-i99n43-Error2_inline.PNG?1559657115">


  • 6.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-04-2019 15:12
    When you set up the calendar report it is looking for the two fields to use for the start and the end of the appointment.

    So you will want to have a Time stamp formula date/time field for the start of the appointment which combines the start date and start time, and then formula date/time field for the end date time.



    The start date/formula would be called perhaps [Start Date/Time] 

    ToTimestamp([Date of Event], [Time of Event])

    [Start Date/Time] + Hours([Total Duration])






  • 7.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 06-05-2019 17:46
    Thank you so much! This worked like a charm!! For future reference for anyone else who needs help with this, here's what I did.

    My Form:
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1792146/RackMultipart20190605-91586-v7n7jk-TimeofAppt_inline.PNG?1559756693">

    Start Date And Time:
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1792142/RackMultipart20190605-98937-1wcmyiv-StartDateTime_inline.PNG?1559756383">
    End Date & Time
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1792143/RackMultipart20190605-86825-2y3hr-EndDateTime_inline.PNG?1559756396">


    Calendar Events Report:


  • 8.  RE: Formula for Calendar Time Block (Start Time + Duration)

    Posted 09-18-2019 13:53
    Eli,

    I just wanted to say thanks for posting how you were able to achieve success. I needed something very similar and your post helped me get to my goal.

    Thanks!!

    ------------------------------
    Andrew Fry
    ------------------------------