Difference between two dates

(0) Share

Report

Posted on by gikido

1,032

Hello everybody ;

I want to create an application that calculates the difference between two date times to calculate the number of employee absences as illustrated in the screenshot below, with the following management rules:

- Working hours are 8:30 a.m. to 1 p.m. and 2 p.m. to 6 p.m.
-Weekends not included.

How can I do this?

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @gikido ,

Please try

Set(StartDateTime,Date(2021,5,7)+Time(8,0,0)); Set(EndDateTime,Date(2021,5,7)+Time(17,0,0)); Sum( ForAll( Sequence(DateDiff(StartDateTime,EndDateTime,TimeUnit.Minutes)/30,1,1), With( {timeTemp:DateAdd(StartDateTime,Value*30,TimeUnit.Minutes)}, If( Weekday(timeTemp)=1 || Weekday(timeTemp)=7 || Value(Text(Hour(timeTemp),"00")&Text(Minute(timeTemp),"00"))<=0830 || Hour(timeTemp)>18 || Hour(timeTemp)=13, {Value:0}, {Value:0.5} ) ) ),Value)

Bof
gikido 1,032 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft thank you for your reply

I tested your code and found errors in calculation cases for example:

Set(StartDateTime;Date(2023;10;5)+Time(17;30;0));;
Set(EndDateTime;Date(2023;10;7)+Time(18;0;0))
gives: 10 while the exact result is 9

Set(StartDateTime;Date(2023;10;5)+Time(17;30;0));;
Set(EndDateTime;Date(2023;10;6)+Time(10;0;0))
gives 2.5 while the exact result is 2
gikido 1,032 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft
Hello ;

are there any updates please?

Thanks
v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @gikido ,

Please try:

Sum( ForAll( Sequence(DateDiff(StartDateTime,EndDateTime,TimeUnit.Minutes)/30,1,1), With( {timeTemp:DateAdd(StartDateTime,Value*30,TimeUnit.Minutes)}, If( Weekday(timeTemp)=1 || Weekday(timeTemp)=7 || Value(Text(Hour(timeTemp),"00")&Text(Minute(timeTemp),"00"))<=0830 || Value(Text(Hour(timeTemp),"00")&Text(Minute(timeTemp),"00"))>1800 || (Value(Text(Hour(timeTemp),"00")&Text(Minute(timeTemp),"00"))>1300 && Value(Text(Hour(timeTemp),"00")&Text(Minute(timeTemp),"00"))<=1400), {Value:0}, {Value:0.5} ) ) ),Value)

Best Regards,

Bof
gikido 1,032 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft
thank you very much it's work now;

Please I forgot a condition, it is that I have a table which contains holidays which I must eliminate and not count it as weekends; The table contains two columns "Id" and "DateDaysFier" of date type only.
How can I integrate this condition into the function you provided me?

THANKS
gikido 1,032 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft

Hello ;

are there any updates please for the last condition please? I'm stuck at this point.

Thanks