Contributor

## Calculate days between dates

Hello all.

I have created an absence list where users can select the dates which by default the time is set to 00:00

It is OK until you use the information to create a calendar.

example: from 27/02/2017 00:00 until 28/02/2017 00:00 > actually means 2 days

because it is the whole day of 27th as well as the whole day of 28th.

but majority of users would class it as 1 day only.

How to overcome this problem?

The form has got From Date and Until Date for selection.

Now, here are the issues;

- some users select only the FROM date example 27/02/2018 00:00 for 1 day

- some users select From 27/02/2018 00:00 Until 27/02/2018 00:00 for 1 day

If the times are not modified then the formula does not know what to do when 00:00 on same day.
Technically if it is 1 day then it would be like this: 27/02/2018 00:00 Until 27/02/2018 23:59

This is so the calendar view can pick up the date & times.

How can I make so the default timing for the UNTIL date is automatically set to 23:55 (that's highest I can select).?

As well as to exclude the weekends days and show only working days.

Examples below:

What is the best approach for this ?!

16 Replies
Collaborator

## Re: Calculate days between dates

what about if you set up a validation rule that would check that date difference is greater than some lowest time period possible to book (eg. 1 hour)?

it would force users to fill in both date controls and change default time values.

Contributor

## Re: Calculate days between dates

just to get the ball rolling... how would I validate the time difference?

if(dateDiffHours(date1, date2)<1)

as per the process: is this an efficient process what I have done so far?

Contributor

## Re: Calculate days between dates

Hi,

I have implemented similar kind of requirement.

First thing is, you have to determine whether or not to capture the time portion?

In my scenario, there are 2 kinds of leave types, full day (will not capture the time portion), partial day (will capture the time portion).

For the above requirement, I created below columns in the list.

1. FullDayBeginDate - Date & Time - Date Only

2. FullDayEndDate - Date & Time - Date Only

If user is applying for One day only, he/she must select Begin Date and End Date.

When entering the leave details in the Calendar, pass the "Event Date" and "End Date" and  "All Day Event", that's it... SharePoint's calendar takes care of it.

Let me know if you need any additional details.

Collaborator

## Re: Calculate days between dates

simply

``dateDiffHours(date1, date2)<1ā``

or better (since dateDiff* functions always return absolute value)

``dateDiffHours(date1, date2)<1 && (date2 > date1)ā``

reg. whether it's not efficient or, I do not know all your requirements.

but very probably I would let users book absence directly to the calendar. they would there decide on their own whether they book just few hours off or all day absence or even repeated absence.

Contributor

## Re: Calculate days between dates

Hi Marian

cheers for the feedback.

the following only works when both dates are the same (dateDiffMinutes(FromDate, UntilDate) < 30)

however if e.g 28/02/2018 00:00 and 29/02/2018 00:00 then the above formula does not work.

I am confused ...

Contributor

## Re: Calculate days between dates

Hey Krishna,

Thanks for the feedback.

The requirements I had was to make a form where users can request, holidays, time off, training, offsite, holiday AM/PM and so forth and so on. At that time, there was no requirement to view the requests in a calendar view

Now since the form is up and running, it has been put forward that it may be good to have a calendar view as well.

With that in mind, my initial form did not ask the users to define the hours, although the date & time were present but nobody bothered to select the times.

When creating the calendar view, as you can imagine, all the requests with dates like this:

from 28/02/2018 00:00 until 29/02/2018 00:00

means that the calendar sees it only one day, although it means to be 2 days from 28th 12am until 29th 11:59pm

So, how to rectify this??

Only thing I can think of is to manually update each request š

In mean time, I am implementing that BOTH dates (FromDate and UntilDate) are mandatory as well as to validate the UntilDate when it is less than 30minutes between prompt user to specify the time as well. see above formula.

In your scenario, you have created the form in a calendar view
which is much better but this was not a requirement in my case but came later ...

Collaborator

## Re: Calculate days between dates

try to calculate with valid date values

Contributor

## Re: Calculate days between dates

I am validating with real values.

the example above was my silly mistake!

from 28/02/2018 00:00 until 1/03/2018 00:00

but the formula does not validate it unless both dates are same.

The validation should check if the UntilDate > TIME is not less than 15min

I am  also trying something like this but is not working:

dateDiffDays(FromDate,UntilDate) > 0 && dateDiffMinutes(FromDate, UntilDate) < 15

which means if FromDate and UntilDate is more than 1 day in difference AND time is less than 15min THEN display error.

Contributor