Hi @Anonymous ,
Do you want to only calculate working hours between two date time?
Could you tell me whether you want to exclude holidays and weekends?
If you do not want to exclude holidays and weekends, things will be easier.
You just need to count the start day's working time, end day's working time ,Interval days' working time and add them up.
I've made a similar test for your reference:
1)use these controls to choose date time: DatePicker1,Dropdown2,Dropdown3,DatePicker2,Dropdown2_1,Dropdown3_1
2)set Dropdown2's Items:
[8,9,10,11,12,13,14,15,16,17]
Dropdown3's Items:
[0,1,2,3,4,5,6,....,59]
Dropdown2_1,Dropdown3_1 are similar
3)use one label to display the result, set the label's Text:
If(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,Days)=0,
If(Dropdown2_1.Selected.Value<17,
DateDiff(DateTimeValue(Text(DatePicker1.SelectedDate&"
"&Dropdown2.Selected.Value&":"&Dropdown3.Selected.Value,ShortDateTime)),
DateTimeValue(Text(DatePicker2.SelectedDate&"
"&Dropdown2_1.Selected.Value&":"&Dropdown3_1.Selected.Value,ShortDateTime)),
Hours
),
DateDiff(DateTimeValue(Text(DatePicker1.SelectedDate&"
"&Dropdown2.Selected.Value&":"&Dropdown3.Selected.Value,ShortDateTime)),
DateTimeValue(Text(DatePicker1.SelectedDate&" "&"17"&":00",ShortDateTime)),
Hours
)
),//if start day and end day is the same day
If(
Dropdown2.Selected.Value=17,
0,
Dropdown2.Selected.Value<17,
DateDiff(DateTimeValue(Text(DatePicker1.SelectedDate&" "&Dropdown2.Selected.Value&":"&Dropdown3.Selected.Value,ShortDateTime)),
DateTimeValue(Text(DatePicker1.SelectedDate&" "&"17"&":00",ShortDateTime)),
Hours) //start day working time
)+
(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,Days)-1)*9+
//interval days' working time
If(
Dropdown2_1.Selected.Value=17,
9,
Dropdown2_1.Selected.Value<17,
DateDiff(
DateTimeValue(Text(DatePicker2.SelectedDate&" "&"8"&":00",ShortDateTime)),
DateTimeValue(Text(DatePicker2.SelectedDate&"
"&Dropdown2_1.Selected.Value&":"&Dropdown3_1.Selected.Value,ShortDateTime)),
Hours
)
)//end day working time
If you want to exclude holidays and weekends, firstly you need to follow the link that @KrishnaV provided to calculate the business day.
Then use this business day to calculate working time.
If(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,Days)=0,
If(Dropdown2_1.Selected.Value<17,
DateDiff(DateTimeValue(Text(DatePicker1.SelectedDate&"
"&Dropdown2.Selected.Value&":"&Dropdown3.Selected.Value,ShortDateTime)),
DateTimeValue(Text(DatePicker2.SelectedDate&"
"&Dropdown2_1.Selected.Value&":"&Dropdown3_1.Selected.Value,ShortDateTime)),
Hours
),
DateDiff(DateTimeValue(Text(DatePicker1.SelectedDate&"
"&Dropdown2.Selected.Value&":"&Dropdown3.Selected.Value,ShortDateTime)),
DateTimeValue(Text(DatePicker1.SelectedDate&" "&"17"&":00",ShortDateTime)),
Hours
)
),//if start day and end day is the same day
If(
Dropdown2.Selected.Value=17,
0,
Dropdown2.Selected.Value<17,
DateDiff(DateTimeValue(Text(DatePicker1.SelectedDate&" "&Dropdown2.Selected.Value&":"&Dropdown3.Selected.Value,ShortDateTime)),
DateTimeValue(Text(DatePicker1.SelectedDate&" "&"17"&":00",ShortDateTime)),
Hours) //start day working time
)+
(business day-1)*9+
//interval days' working time
If(
Dropdown2_1.Selected.Value=17,
9,
Dropdown2_1.Selected.Value<17,
DateDiff(
DateTimeValue(Text(DatePicker2.SelectedDate&" "&"8"&":00",ShortDateTime)),
DateTimeValue(Text(DatePicker2.SelectedDate&"
"&Dropdown2_1.Selected.Value&":"&Dropdown3_1.Selected.Value,ShortDateTime)),
Hours
)
)//end day working time
You need to use business day to calculate interval days' working time.
Best regards,
