How to calculate available Leave day in SharePoint List

Nguyen Huu 101

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?

Thanks.

Accepted answer

JoyZ 18,041 Reputation points

2021-04-27T07:26:16.617+00:00
Hi @Nguyen Huu ,

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

My sample list structure with column types:

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

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:

Result:

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:

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.
Please sign in to rate this answer.

1 person found this answer helpful.
Antoka 0 Reputation points

2023-09-12T22:59:41.34+00:00

Hi @JoyZ , I have a similar issue. The steps mentioned above work perfectly! However, I need to do the calculations per user who applied who has different leave days per year. Additionally, I have to exclude public holidays and not count them as leave days. is that possible?

Thank you

Antonios
Sign in to comment

4 additional answers

JoyZ 18,041 Reputation points

2021-04-05T08:04:04.637+00:00

Hi @Nguyen Huu ,

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:

Flow steps:

Result:

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.
Please sign in to rate this answer.
Nguyen Huu 101 Reputation points

2021-04-06T01:32:59.967+00:00

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

JoyZ 18,041 Reputation points

2021-04-07T09:32:22.877+00:00

Hi @Nguyen Huu ,

I have updated the answer, please check it.

Nguyen Huu 101 Reputation points

2021-04-07T10:22:15.663+00:00

Thank so much for your updated @JulieWang-MSFT ,
but it work not correct:

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

Thanks.

Nguyen Huu 101 Reputation points

2021-04-07T15:00:20.937+00:00

After added your step to my Flow, it doesn't run also:

JoyZ 18,041 Reputation points

2021-04-08T08:42:36.76+00:00

Hi @Nguyen Huu ,

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)))

Nguyen Huu 101 Reputation points

2021-04-08T09:07:37.703+00:00

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

and how to allow my flow after calculate " your available leave day"?

JoyZ 18,041 Reputation points

2021-04-08T09:41:53+00:00

Hi @Nguyen Huu ,

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

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

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

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

Nguyen Huu 101 Reputation points

2021-04-08T10:07:07.863+00:00

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:

Thank you so much.

JoyZ 18,041 Reputation points

2021-04-09T06:39:38.657+00:00

Hi @Nguyen Huu ,

I add a new column named "Manager" using "Yes/No" column type:

Then use the flow template "Start approval when a new item is added", remove actions unnecessary to make the flow like yours:

The flow still works well as shown below:

Nguyen Huu 101 Reputation points

2021-04-12T07:49:51.323+00:00

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.

JoyZ 18,041 Reputation points

2021-04-12T08:13:07.43+00:00

Hi @Nguyen Huu ,

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.

JoyZ 18,041 Reputation points

2021-04-16T02:25:17.09+00:00

Hi @Nguyen Huu ,

Do you have any progress on this issue?

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

Nguyen Huu 101 Reputation points

2021-04-16T02:52:51.067+00:00

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.

JoyZ 18,041 Reputation points

2021-04-26T06:42:27.927+00:00

Hi @Nguyen Huu ,

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.

Nguyen Huu 101 Reputation points

2021-04-26T09:13:13.753+00:00

Hi @JulieWang-MSFT ,
Sorry for my late respond, i tried to create new form to test but the column "Sum" still can't load the value:

Nguyen Huu 101 Reputation points

2021-04-26T09:28:15.463+00:00

add more pics:

and how to calculate if staff just off half day? current "Total day leave" =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)))

Thank you so much.
Sign in to comment
Nguyen Huu 101 Reputation points

2021-04-27T08:16:43.913+00:00

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 ?

Thanks.
Please sign in to rate this answer.
JoyZ 18,041 Reputation points

2021-04-27T08:58:32.473+00:00

Hi @Nguyen Huu ,

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.

Nguyen Huu 101 Reputation points

2021-04-27T09:08:56.683+00:00

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.

Nguyen Huu 101 Reputation points

2021-04-27T09:23:51.003+00:00

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.

JoyZ 18,041 Reputation points

2021-04-27T09:30:03.09+00:00

Hi @Nguyen Huu ,

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

Nguyen Huu 101 Reputation points

2021-04-27T09:31:40.04+00:00

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

Nguyen Huu 101 Reputation points

2021-05-12T04:15:05.997+00:00

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

JoyZ 18,041 Reputation points

2021-05-12T08:35:28.27+00:00

@Nguyen Huu ,

Sorry for the late reply, please refer to:

=IF([halfday]=true,((DATEDIF([StartDate],[EndDate],"D"))-INT(DATEDIF([StartDate],[EndDate],"D")/7)*2- IF(WEEKDAY([EndDate])<WEEKDAY([StartDate]),2, IF(OR(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=1),1,0))+1)-0.5,((DATEDIF([StartDate],[EndDate],"D"))-INT(DATEDIF([StartDate],[EndDate],"D")/7)*2- IF(WEEKDAY([EndDate])<WEEKDAY([StartDate]),2, IF(OR(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=1),1,0))+1))

The halfday is a Yes/No type column.

Reulst for your reference:

Nguyen Huu 101 Reputation points

2021-05-12T15:34:41.233+00:00

Thank you so much @JulieWang-MSFT ,
it work as i expected.
Have a nice day!
Sign in to comment
Nguyen Huu 101 Reputation points

2021-05-20T08:24:24.85+00:00

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?

Thank you so much.
Please sign in to rate this answer.
Sonam Subhadarsini 1 Reputation point

2021-10-18T13:34:24.667+00:00

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
Sign in to comment
Santosh K Ghosh 0 Reputation points

2023-06-20T05:58:57.0466667+00:00

I do have similar problem, but I want once leave is approved by approver using email, a flow will run to update another sharepoint list that store the leave balance. Prompt advice and help would be appreciated.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment