Overview

In this business scenario we want to measure the duration of the lunch break on Appointments. We have two custom option set fields: Lunch-break start time and Lunch-break finish time. These are made up of 15-minute time slots, for example 12:00, 12:15, 12:30, 12:45, 13:00.

We want to put the difference of these two "times" into another custom field called Lunch-break duration.

We will do this using North52's Split function in conjunction with its DateDiff function. 

 

North52 Decision Suite

The North52 Decision Suite solution works like this:

• A Formula is set up that triggers whenever one of the Appointment's Lunch-break start time or Lunch-break finish time fields are changed
  • This formula will split the hours from the minutes for each of the option sets
  • It will then create a DateTime type for each of these values
    • Both DateTimes will have the same year, month and day - only the hour and minutes will be different
  • The formula will calculate the difference between these two DateTimes
  • Finally, the Lunch-break duration field will be updated with the difference between both DateTimes

Formula

• Create a new formula, setting the following values in the Formula Guide:
  • Set Entity to Appointment
  • Set Formula Type to Save - Perform Action
  • Set Editor to Decision Table
• Click Create
• Change the Name of the formula to Calculate time spent on lunch break using option sets
• Expand Source & Target
  • Under Source Property, select Lunch-break start time and Lunch-break finish time 
    •  Note 1: you multi-select fields by holding down Ctrl when clicking
    •  Note 2: These are custom fields and they will not appear unless you've already created them
• Click onto the Decision table editor
  • Select cell A2
    • Right-click and select Delete > Delete Column
  • Right-click and select Operations > Toggle Advanced Mode
  • Select the Global Calculations sheet
    • For Calculation Name enter Start and Finish time differences 
    • For Calculation Value, click the + in the top-right corner and enter the formula below:

      Smartflow(
      
        SetVar('Start_Hour',Split([appointment.new_lunchbreakstarttimename],':',0)),
        SetVar('Start_Minute',Split([appointment.new_lunchbreakstarttimename],':',1)),
        SetVar('Start_Time', CreateDate(2019,1,1,GetVar('Start_Hour'),GetVar('Start_Minute'))),
        
        SetVar('Finish_Hour',Split([appointment.new_lunchbreakfinishtimename],':',0)),
        SetVar('Finish_Minute',Split([appointment.new_lunchbreakfinishtimename],':',1)),
        SetVar('Finish_Time', CreateDate(2019,1,1,GetVar('Finish_Hour'),GetVar('Finish_Minute'))),
        
        DateDiff(GetVar('Finish_Time'), GetVar('Start_Time'),'mi'))

    • Close the advanced editor by clicking on the + again
      
• Select cell A2
  • Go to the Source tab and expand Source.
  • Find and select Lunch-break duration
• Select cell cell A4
  • Go to the Source tab and collapse Source
  • Expand Global Calculations and select Start and Finish times difference
• Click Save
  

Testing

To test this formula, simply create or update an Appointment's Lunch-break fields.

Note you may need to add the fields to the Appointment's form.