cancel
Showing results for
Did you mean:
Super User

## Calculate Half Day

Hi, I'm trying to calculate Half-day if the box is checked in the Total Days Applied.

This is a Sharepoint form integrated with Power Apps. The Total Days Applied calculates the from the StartDate until EndDate, then I want if the user checks the Half-day box, it will calculate the half-day. For example, the StartDate is 6/27/2022 and the EndDate is 6/27/2022 so the Total Days Applied is 1, if the user checks the half-day box, the Total Days Applied will become 0.5 days. How to calculate that?

This code is the calculation for the Total Days Applied excluding weekends and public holidays.

``````With(
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)
},
If(
And(
IsBlank(DataCardValue2.SelectedDate),
IsBlank(DataCardValue17.SelectedDate)
),
// show nothing if any date pickers are blank
0,
// show only dates Monday to Friday and exclude holidays
CountIf(
varDateRange,
And(
Weekday(Value) in [2, 3, 4, 5, 6],
Not(Value in Holidays.StartDate)
)
)
)
)``````

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

``````With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)},

If(

IsBlank(DataCardValue2.SelectedDate) || IsBlank(DataCardValue17.SelectedDate),
0,

DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && Toggle1.Value,
0.5,

DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && !Toggle1.Value,
1,

!IsBlank(DataCardValue2.SelectedDate) && !IsBlank(DataCardValue17.SelectedDate) && Toggle1.Value
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) ) / 2

CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)``````
7 REPLIES 7
Super User

I assume that users cannot select half day when start date and end date are not the same and your formula works well except half day toggle on.

``````With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)},

If(

IsBlank(DataCardValue2.SelectedDate) && IsBlank(DataCardValue17.SelectedDate), .
0,

DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && Toggle1.Value,
0.5,

CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)``````

Super User

"I assume that users cannot select half day when start date and end date are not the same"

- For this one, I want the calculation to apply for StartDate and EndDate as well. For example, the StartDate is 28/6/2022 and the EndDate is 30/6/2022 and the Half Day toggle is on so the Total Days Applied is 1.5 because there are 3 days the user applied for Half Day.

Super User

``````With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)},

If(

IsBlank(DataCardValue2.SelectedDate) || IsBlank(DataCardValue17.SelectedDate),
0,

DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && Toggle1.Value,
0.5,

DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && !Toggle1.Value,
1,

!IsBlank(DataCardValue2.SelectedDate) && !IsBlank(DataCardValue17.SelectedDate) && Toggle1.Value
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) ) / 2

CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)``````
Super User

@Alex_10 It works, thank you!

Frequent Visitor

Hello Sir,

Im getting error while using this code:

With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(Enddate.SelectedDate - Startdate.SelectedDate + 1),
Startdate.SelectedDate + Value - 1
)
},

If(

IsBlank(Startdate.SelectedDate) || IsBlank(Enddate.SelectedDate),
0,

Startdate.SelectedDate = Enddate.SelectedDate && Toggle1.Value,
0.5,

Startdate.SelectedDate = DataCardValue17.SelectedDate && !Toggle1.Value,
1,

!IsBlank(Startdate.SelectedDate) && !IsBlank(Enddate.SelectedDate) && Toggle1.Value
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) ) / 2

CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)

Frequent Visitor

Hello Sir,

Today the formula worked. Great.

Sir if employee want leave from 20-09-2022 - 22-09-2022. Where if they need to take half day in all 3 days , how can i do that ?

Frequent Visitor

Here in this formula , suppose end date = start date = 17/11/2022 [ if 17 is a holiday ]

the formula is returning 1 as a final value.

can you advise how we can solve it?

Announcements

#### December 2023 User Group Update: Welcoming New Groups and Upcoming Events

A new month means it's time to celebrate and welcome the new user groups that have joined our community. We are excited to announce that we have more than 8 New Groups, which is no surprise after the amazing Microsoft Power Platform Conference. This month, we are breaking them out by the different community categories. If your group is listed here, give this post a kudo so we can celebrate with you! Don't forget to take a look at the many events happening near you or virtually! It's a great time of year to connect and engage with User Groups both locally and online.   Please Welcome Our NEW User Groups   Power Platform: PowerIT User Group: Nottingham Power Platform User Group: Bergen Power Platform User Group: Danmark Nashville Power Platform User Group Microsoft Ambassador Program y Mujer Latina Technolochicas NCWIT Community Copilot Studio:  Copilot User Group Italia Dynamics365: Dynamics User Group AdriaticDynamic 365 Azerbaijan   December User Group Events   01 Dec 2023 Aprendiendo Desarrollo web, creando mi primer power app y power page. 01 Dec 2023 Q4 Hybrid Philadelphia Dynamics 365 & Power Platform User Group Meeting05 Dec 2023APAC Dynamics 365 FastTrack Bootcamp - BI and Analytics07 Dec 2023Bay Area Power Platform Meetup: Learn, Share, and Connect07 Dec 2023Indiana D365/AX December User Group Meeting07 Dec 2023Dynamics User Group Meeting: Houston09 Dec 2023 December '23 - NEW Power Apps User Group Meeting - Online 12 Dec 2023December Cleveland Power Platform User Group Meeting12 Dec 2023 RW2 Data Stewardship Open Forum Discussion 13 Dec 2023  Black Country Power Platform User Group - December 2023 - West Midlands