question

NguyenHaSonHuu-5585 avatar image
0 Votes"
NguyenHaSonHuu-5585 asked SonamSubhadarsini-4807 commented

How to calculate available Leave day in SharePoint List

Hi All,
I create Leave request for staff by SharePoint List but i don't know how to calculate available Leave day for next request. Default staff have 12 days per year, if day left 2 days ,they will have 10 available Leave day for next request,
i put calculate available leave day by: 12- [Total day leave] but it don't work as i requirement so can you please help to share how can i put at Formula to get correct number?
84252-image.png


84251-image.png


Thanks.

office-sharepoint-online
image.png (10.5 KiB)
image.png (19.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoyZ avatar image
0 Votes"
JoyZ answered

Hi @NguyenHaSonHuu-5585,

To avoid confusion, I will post a new reply for your reference.

My sample list structure with column types:

91467-image.png

Calculated column syntax(Your available leave day and total day leave):

91624-image.png91625-image.png

total day leave formula:

 =IF(AND((WEEKDAY([End date],2))<(WEEKDAY([Start date],2)),((WEEKDAY([Start date],2))-(WEEKDAY([End date],2)))>1),(((DATEDIF([Start date],[End date],"D")+1))-(FLOOR((DATEDIF([Start date],[End date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start date],[End date],"D")+1))-(FLOOR((DATEDIF([Start date],[End date],"D")+1)/7,1)*2)))

Flow:

91598-image.png
91615-image.png
91630-image.png
91643-image.png

Result:

91682-image.png
Note:

Since there are spaces between my column names, so the internal name will have x0020, if your column has no spaces, remember to remove it.

You could also check the internal name of the column via list settings>click the column> check the internal name in URL like this:
91662-image.png

Finally, if you want to calculate half day, you need to calculate based on hours, which is difficult to achieve in the subsequent process.


If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (23.7 KiB)
image.png (6.1 KiB)
image.png (38.8 KiB)
image.png (56.8 KiB)
image.png (40.2 KiB)
image.png (55.2 KiB)
image.png (47.5 KiB)
image.png (15.3 KiB)
image.png (22.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoyZ avatar image
0 Votes"
JoyZ answered NguyenHaSonHuu-5585 commented

Hi @NguyenHaSonHuu-5585,

Calculated fields can only operate on their own row, so we can't reference a value in another row, or columns contained in another list or library.

So the available leave day will not be updated based on other row values.

----------------------------------------------------------Update------------------------------------------------------------

As a workaround, we could create a new column named "Sum", use flow to update sum column automatically, then calculate available leave day based on this column.

List example:

85253-image.png

Flow steps:

85170-image.png
85236-image.png
85198-image.png

Result:

85255-image.png


If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (5.9 KiB)
image.png (54.7 KiB)
image.png (37.3 KiB)
image.png (75.7 KiB)
image.png (28.6 KiB)
· 16
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI @JulieWang-MSFT ,
So Do you have any solutions for this?

0 Votes 0 ·
JoyZ avatar image JoyZ NguyenHaSonHuu-5585 ·

Hi @NguyenHaSonHuu-5585,

I have updated the answer, please check it.

0 Votes 0 ·

Thank so much for your updated @JulieWang-MSFT ,
but it work not correct:
85302-image.png
85282-image.png
85229-image.png
85257-image.png
85303-image.png

more questtion: how can i calculate total day leave from Start day and End day ( not include weekend) ?

Thanks.

0 Votes 0 ·
image.png (22.0 KiB)
image.png (47.4 KiB)
image.png (37.2 KiB)
image.png (26.1 KiB)
image.png (19.0 KiB)

After added your step to my Flow, it doesn't run also:
85310-12.png


0 Votes 0 ·
12.png (28.3 KiB)
JoyZ avatar image JoyZ NguyenHaSonHuu-5585 ·

Hi @NguyenHaSonHuu-5585,

From your screenshot, the sum column seems not to be updated.

Do you change the calculated column(your available leave day) to 12-Sum, rathan than 12- totaldayleave?

In addition, this flow will only be triggered when we create a new item, you could change the trigger for your requirement.

Calculate total day leave from Start day and End day (not include weekend):

 =IF(AND((WEEKDAY([End date],2))<(WEEKDAY([Start date],2)),((WEEKDAY([Start date],2))-(WEEKDAY([End date],2)))>1),(((DATEDIF([Start date],[End date],"D")+1))-(FLOOR((DATEDIF([Start date],[End date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start date],[End date],"D")+1))-(FLOOR((DATEDIF([Start date],[End date],"D")+1)/7,1)*2)))


0 Votes 0 ·

Thanks for your feedback @JulieWang-MSFT ,
Do we need to setting something at Sum column? because it still not work:
85741-sum.png


and how to allow my flow after calculate " your available leave day"?
85698-flow.png


0 Votes 0 ·
sum.png (30.9 KiB)
flow.png (78.9 KiB)
JoyZ avatar image JoyZ NguyenHaSonHuu-5585 ·

Hi @NguyenHaSonHuu-5585,

If your total day leave is a calculated column, change the increment varilable in Expression like this:

85772-image.png

 float(items('Apply_to_each')?['totaldayleave'])

Remove the Filter Query, since calculated column can not to be used in this condition:

85733-image.png

Please make sure that the previous process is working well before considering the next step.

0 Votes 0 ·
image.png (19.3 KiB)
image.png (4.7 KiB)

Hi @JulieWang-MSFT ,
After changed "Increment variable" my Flow run again, but there is not working at " Your available leave day", there is my flow:85783-1.png85801-2.png85792-3.png


85793-4.png


Thank you so much.

0 Votes 0 ·
1.png (7.5 KiB)
2.png (46.3 KiB)
3.png (47.2 KiB)
4.png (47.7 KiB)
Show more comments

Hi @JulieWang-MSFT ,
Sorry for my late respond, i followed your step but i don't know why there is not work at Column " Sum" ,therefore "Your Available leave day" not work.

Thanks.
Nguyen Huu.

0 Votes 0 ·
JoyZ avatar image JoyZ NguyenHaSonHuu-5585 ·

Hi @NguyenHaSonHuu-5585,

Whether the flow has no error message, however the list not updated?

Please try to create a new simple test list, then create the flow again to check the result.

0 Votes 0 ·

Hi @NguyenHaSonHuu-5585,

Do you have any progress on this issue?

Please remember to update this thread if you need further assistance.

0 Votes 0 ·

Thanks for your remind @JulieWang-MSFT ,
I will arrange time to test as your recommended and update to you soon.
Have a nice day!
Nguyen Huu.

0 Votes 0 ·
JoyZ avatar image JoyZ NguyenHaSonHuu-5585 ·

Hi @NguyenHaSonHuu-5585,

A few days have passed, and looking forward to your feedback.

If you feel a answer works for you, please kindly accept it as answer.

It will be beneficial to other community members reading this thread.

Thank you for your understanding.

1 Vote 1 ·
Show more comments
NguyenHaSonHuu-5585 avatar image
0 Votes"
NguyenHaSonHuu-5585 answered NguyenHaSonHuu-5585 commented

Hi @JulieWang-MSFT ,
Thank so much for your feedback.
as follow your steps, it work now.
Relate to calculate half day, can we calculate by: one day =1, half day=0,5? Can we create more column " Half day", if user select Yes, that day will count 0,5 date ?
91470-7.png91701-8.png



Thanks.


7.png (9.1 KiB)
8.png (20.1 KiB)
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NguyenHaSonHuu-5585,

I think this is not feasible.

As mentioned at the beginning, the calculated column is only applicable to each item, if the "Sum" column does not add 0.5, the previous 0.5 will not be recorded.

91640-image.png


0 Votes 0 ·
image.png (19.6 KiB)

Thanks @JulieWang-MSFT ,
Some user just want to leave half day so if possible would you please share me how to calculate at "Total day leave" column with half day? base on requirement of if department i will consider to remove or keep column Sum and Your Available Leave Day column.

Thanks.

0 Votes 0 ·

Hi @JulieWang-MSFT ,
Last time i calculate half day at Total day leave but can't remove the weekend:
=IF(HalfDay+False,Enddate-Startdate,(if(Enddate-Startdate.0,(Enddate-Startdate)-0.5,0.5)))

Thanks.

0 Votes 0 ·
JoyZ avatar image JoyZ NguyenHaSonHuu-5585 ·

Hi @NguyenHaSonHuu-5585,

I will look into this issue and will give you an update if I have any progress.

0 Votes 0 ·

Thank so much @JulieWang-MSFT ,
Have a nice day!

0 Votes 0 ·

Hi @JulieWang-MSFT ,
Do you have any update relate to calculate half day?
Thanks.

0 Votes 0 ·
Show more comments
NguyenHaSonHuu-5585 avatar image
0 Votes"
NguyenHaSonHuu-5585 answered SonamSubhadarsini-4807 commented

Hi @JulieWang-MSFT ,
When i use " Total day leave " in "Send email with option", there are many number "0" at email notification so do you know how to remove it? and How to change the day to DD-MM-YYYY?
98224-image.png


98136-image.png


Thank you so much.


image.png (66.9 KiB)
image.png (44.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Hi, Please have a look at this below blog. It definitely helps you.

https://www.spguides.com/convert-decimal-to-whole-number-power-automate/


Thanks

0 Votes 0 ·