cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
bimi82
Nintex Newbie

Calculate days between dates

Jump to solution

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.

The calculated value has got the formula copied from this page

https://community.nintex.com/thread/1879

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

Labels: (1)
Tags (1)
0 Kudos
Reply
16 Replies
emha
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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.

0 Kudos
Reply
bimi82
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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?
or would you advise something else instead?

0 Kudos
Reply
krishnachaluvad
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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.

Reply
emha
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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.

Reply
bimi82
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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

Reply
bimi82
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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

0 Kudos
Reply
emha
Nintex Newbie

Re: Calculate days between dates

Jump to solution

try to calculate with valid date values

0 Kudos
Reply
bimi82
Nintex Newbie

Re: Calculate days between dates

Jump to solution

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.

0 Kudos
Reply
bimi82
Nintex Newbie

Re: Calculate days between dates

Jump to solution

Update:

I think this is much complicated as I initially thought.
Because, if I validate the following

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

than all it does it takes the 1440 minutes into account and as soon as you go over 2 or 3 days then the minutes are not valid because the gap is much wider between the dates.

Unless there is a way asking the TIME not to be 00:00 for the UntilDate via JavaScript ?!
Do let me know guys

0 Kudos
Reply