# question

## 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?

Thanks.

image.png (10.5 KiB)
image.png (19.1 KiB)

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

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.

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)

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

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:

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

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?

NguyenHaSonHuu-5585

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.

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

12.png (28.3 KiB)
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)))
``````

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"?

sum.png (30.9 KiB)
flow.png (78.9 KiB)
NguyenHaSonHuu-5585

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.

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:

Thank you so much.

1.png (7.5 KiB)
2.png (46.3 KiB)
3.png (47.2 KiB)
4.png (47.7 KiB)
NguyenHaSonHuu-5585

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.

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.

Do you have any progress on this issue?

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.

NguyenHaSonHuu-5585

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

1 Vote 1 ·

Hi @JulieWang-MSFT ,
Thank so much for your feedback.
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.

7.png (9.1 KiB)
8.png (20.1 KiB)
· 8

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

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.

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.

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.

NguyenHaSonHuu-5585

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

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

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

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.

image.png (66.9 KiB)
image.png (44.8 KiB)
· 1

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.

Thanks