3

One of the Projects I am working on part of a list form, I have two date fields as Start Date and End Date which users have to populate when they submit the form. The challenge I am facing here is to disable weekday selection and make sure only weekend dates are allowed to enter.If there is no way to disable the weekday dates, then I would like to at least show a custom validation message that would prompt user as entered date is invalid.

Taking one step ahead, based on the inputs in above two fields, I would like to create a third column of type calculated which would show a number value based on the date population in the above field.

Let's say if user enters 22nd and 23rd June as Weekend dates in start date and end date respectively then the calculated field would show value as 1 (not two), Saturday and Sunday would equal to 1 credit and if either only Saturday or Sunday date is entered, for example if a user enters 22nd June as start date and end date then the calculated column would show value as .5 (since employee worked for only one day). Third case would be let's say if an employee works for 2 weekends, then they would enter start and end dates as 6/9, to 6/17, now this date period would involve 2 Saturdays and 2 Sundays so the calculated column value would be shown as 2 (.5 for each weekend day). This is meant for employees who travel to different cities and part of a company event, they have to work on a weekend day, for which they get credit and that is exactly what we are trying to achieve here. Also, they only get credit if they work on a weekend not on a weekday. The only exception is if an employee works on a weekday which is a holiday for example 4th of July which is a weekday holiday and if an employee works on 4th of July then they would get .5 credit for that day as well so if possible I would like to account for holiday as well which falls on to weekday.

Please help with JS/JQ approach for this as I am working with SharePoint online.

  • The js approach will only work on classic lists, and you will have to use something like managed metadata nav in order to ensure that the list can only be shown in classic mode. Otherwise any user can change to to modern and bypass your js validation – Kasper Bo Larsen Jun 28 '18 at 19:25
3

Well, to test a column for Saturday or Sunday, you can update each columns validation section with

=OR(TEXT([DATEFIELD],"ddd")="Sat", TEXT([DATEFIELD],"ddd")="Sun")

And enter a message like 'Date must be Saturday or Sunday'.

For your other calculated field, something like this would work. Each date gets .5 day credit if Sat/Sun - if both are weekends, it will add up to 1.

 =IF(OR(TEXT([DATEFIELD1],"ddd")="Sat", TEXT([DATEFIELD1],"ddd")="Sun"),.5,0) + 
IF(OR(TEXT([DATEFIELD2],"ddd")="Sat", TEXT([DATEFIELD2],"ddd")="Sun"),.5,0)

A start to figuring it out for a range of weeks. I found this in an Excel page, it's kind of tricky. This is the Excel format:

Number of Sundays   =INT((WEEKDAY(End-1)+End-Start)/7)

This may take care of the edge cases - I've also found that INT does work in SharePoint, and I messed up the above calcs by using the EndDate in the wrong place. Give this a try:

=(INT((WEEKDAY(StartDate-1)+EndDate-StartDate)/7)*0.5)+
(INT((WEEKDAY(StartDate-7)+EndDate-StartDate)/7)*0.5)

I've tested this with 6/23/2018 to 6/24/2018 (1 sat/1 sun), 6/22-6/25 (1 sat, 1 sun), Mon-Fri, Mon-Sat, Mon-Sun, and a multi week span 7/14 - 7/29 (3 sat, 3 sun) and it seems to work.

  • Note that "TEXT([DATEFIELD],"ddd")="Sat"" will also be true if DATEFIELD is blank. – Mike Smith - MCT - MVP Jun 28 '18 at 20:13
  • Hi Robin, The first validation logic worked for me, thanks a lot for that but the second one for calculated column does not give me the correct result. For example I entered Start date and end date as follows: 6/9/2018, 6/24/2018. The calculated column shows me only 1 as value, instead it should show 3 since the date period includes 3 saturday and 3 sundays, any suggestions? – mdevm Jun 29 '18 at 13:54
  • ah, no, that would be pretty complex - don't have an answer off the top of my head. Maybe something using DateDiff(date1,date2,"D"). Let me think about it.. – Robin in Or Jun 29 '18 at 17:53
  • Hi Robin, Can you please check the updated and bolded description in the question? Thanks for your help thus far, theabove formula would work but I have a doubt. What if the start and end date dates are same? This means employee works only for a day, would it work? Also start date could be saturday and end date could be sunday, would it still work? – mdevm Jul 2 '18 at 13:40
  • I've updated the calcs in my answer - I think this will work with all of your cases - except for holidays - there's no way for a calculated field to pick up when a holiday is since it varies per country/state. – Robin in Or Jul 2 '18 at 15:57
2

For a validation column that is more language independent use the WEEKDAY function:

=OR( AND(NOT(ISBLANK( [yourDateColumn] )), WEEKDAY( [yourDateColumn], 2 )=6),
     WEEKDAY([yourDateColumn],2)=7 ) 

Note: the ISBLANK test is needed as the WEEKDAY for a blank date is "Saturday".

If this "if either only saturday or sunday date is entered" means that one or the other is blank:

=IF( AND( WEEKDAY( [StartDate], 2 )=6, NOT(ISBLANK( [StartDate])),WEEKDAY([EndDate],2)=7 ),
         1,
         IF( OR(
                AND(ISBLANK( [StartDate] ), WEEKDAY([EndDate],2)=7),
                AND(ISBLANK( [EndDate] ), NOT(ISBLANK( [StartDate])),WEEKDAY([StartDate],2)=6)
               ),
               5,
               "what ever you need if none of the above"))
  • Good point to use the weekday instead of the name of the day - I always forget to internationalize.... – Robin in Or Jun 28 '18 at 20:46
  • Hi Mike, I tried the syntax you gave me for the Weekday function, but I am kept on receiving syntax error, any suggestions? – mdevm Jun 29 '18 at 13:41
  • The first formula was missing a ")" for the NOT. I updated the answer. – Mike Smith - MCT - MVP Jun 29 '18 at 14:57
  • Great thanks Mike that worked. The second formula you provided for the calculated column does not show the right value. For example, I put in Start Date and End Date as follows: 6/16/2018,6/24/2018 respectively. This date period includes 2 saturdays and 2 sundays so 2 weekends. In theory the calculated column should show value as 2 (saturday + sunday =1) but it shows 1 instead. Any suggestions? Also, if start date and end date is same for example 6/16/2018 then it shows value as "NO" instead it should show 0.5 since users will enter same value in both date fields it they only worked for a day. – mdevm Jun 29 '18 at 19:51
  • Sorry, that's not how I interpreted your request. Can you give an expanded example of what you need? I think both Robin and I have missed what you are trying to do. If this time recording for work that can only occur on weekends? Do partial days need to be considered? Can work only start on Saturday, and only end on Sunday? (Johnny 5 needs input!) – Mike Smith - MCT - MVP Jun 30 '18 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.