Solved

Last week day of the month expression

  • 16 October 2020
  • 2 replies
  • 35 views

Badge +1

Hello, I am new to K2 and I am trying to write an expression on a text box on a form to display a payroll cut off date which is the last week day of the month. I can get the last day of the month using the built in end of month operator but I am struggling to subtract days if the last day of the month is a Saturday or a Sunday. I have tried using an If weekday is Saturday then subtract days of 1 or if weekday is Sunday subtract 2 days but this results in a False being displayed. Has anyone done something similar or can advise the best way to achieve this? I also thought I would need a else just display the date but I don’t see an operator of Else.

icon

Best answer by Prineel 16 October 2020, 15:18

Hi KScobie, 

 

I have a solution, its a bit complex, but I will try my best to explain:

Please read all the way to the end before attempting, as the very end gives you another way to do it!!!

So firstly I recommend that you create a dataLabel or a seperate textbox to store the last day of the month date. I used a seperate text box. Here is how the expression will look like (Calender control with date set to “Current Date”):

 

I have created 2 texboxes (NOTE: You should change their Data type to “Date” in their properties), one that contains the last day of the month (from the expression above), and the second one will contain the last day of the month that is not a weekend:

 

So, the “Weekday” operator converts a date (2020/10/16) into a day in text (Firday) :

 

Your expression (if you use 2 different textboxes like I have) would be something like this (We are basically converting the date into a text day and saying if it equals to either Saturday or Sunday, then - 1 or -2 dates respectively. Else display the last day of the month):

As you can see by my horrible “Else”, the last  part of this specific IF statement is the else part. If you hover over the IF operator, it will show you how the conditions and else parts work 

 

The result should look something like this:

NOTE: If you do not want to use both textboxes and just one then you will have to use the end of month operator infront of each textbox input (if that makes any sense). It will look like this:

 

Hope this helps, please reach out if you need any explanations/help.

 

Kind Regards

Prineel

View original

2 replies

Badge +1

Thank you so much. I now have this working.

Userlevel 1
Badge +5

Hi KScobie, 

 

I have a solution, its a bit complex, but I will try my best to explain:

Please read all the way to the end before attempting, as the very end gives you another way to do it!!!

So firstly I recommend that you create a dataLabel or a seperate textbox to store the last day of the month date. I used a seperate text box. Here is how the expression will look like (Calender control with date set to “Current Date”):

 

I have created 2 texboxes (NOTE: You should change their Data type to “Date” in their properties), one that contains the last day of the month (from the expression above), and the second one will contain the last day of the month that is not a weekend:

 

So, the “Weekday” operator converts a date (2020/10/16) into a day in text (Firday) :

 

Your expression (if you use 2 different textboxes like I have) would be something like this (We are basically converting the date into a text day and saying if it equals to either Saturday or Sunday, then - 1 or -2 dates respectively. Else display the last day of the month):

As you can see by my horrible “Else”, the last  part of this specific IF statement is the else part. If you hover over the IF operator, it will show you how the conditions and else parts work 

 

The result should look something like this:

NOTE: If you do not want to use both textboxes and just one then you will have to use the end of month operator infront of each textbox input (if that makes any sense). It will look like this:

 

Hope this helps, please reach out if you need any explanations/help.

 

Kind Regards

Prineel

Reply