0

I have a date field called Start Date. I am using column validation to allow users to select only 4 date values, if they enter other than this they see a validation error. So based on this Start Date Column, I have created a Calculated field called "Holiday Approval Credit", now the logic for this calculated field is like: If a user enters any of the above 4 dates, then they should see 0.5 as value in the Calculated field. So maybe something like this: =IF(Start Date="10/8/2018",0.5, IF(Start Date="9/3/2018",0.5)... Value would be 0 for dates other than these 4 dates. Can someone please help me with the correct formula, thanks in advance.

=(INT((WEEKDAY([Start Date]-1)+[End Date]-[Start Date])/7)*0.5)+(INT((WEEKDAY([Start Date]-7)+[End Date]-[Start Date])/7)*0.5)

Improve this question

1 Answer 1

Reset to default
1

The formula you have above won't work because of the way Date fields are stored and displayed. The formula you want is:

=IF(OR([Start Date]=DATE(2018, 10, 8), [Start Date]=DATE(2018, 9, 3), [Start Date]=DATE(YEAR, MONTH, DAY), [Start Date]=DATE(YEAR, MONTH, DAY)), 0.5, 0)

Let me know if this works for you. :)

Best,

Chris Dennett

Improve this answer
7
  • Thanks a lot Dennett. I have a quick question for you, my apologies first, the date field name is Holiday not Start date. I do have Start Date and End Date as two separate date fields. I am already using a Calculated field to show value based on the formula I added into the Question Description. Can I simply add your formula to the above? I am not good at Syntax so wanted to make sure.
    – mdevm
    Jul 16, 2018 at 17:33
  • Hello mdevm. I'm not quite sure what you're trying to accomplish overall. The formula you added above seems to be calculating the number of weeks between start and end date, maybe? If you just want to add this 0.5 to that calculation, then yes you can simply add the IF condition to the end, like so: =(INT((WEEKDAY([Start Date]-1)+[End Date]-[Start Date])/7)*0.5)+(INT((WEEKDAY([Start Date]-7)+[End Date]-[Start Date])/7)*0.5)+IF(OR([Start Date]=DATE(2018, 10, 8), [Start Date]=DATE(2018, 9, 3), [Start Date]=DATE(YEAR, MONTH, DAY), [Start Date]=DATE(YEAR, MONTH, DAY)), 0.5, 0). That should work.
    – C. Dennett
    Jul 16, 2018 at 17:55
  • The Formula you provided does not work. I simply changed Start Date to Holiday as that is what i have named the date field, here is the formula: =IF(OR([Holiday]=DATE(2018,10,8), [Holiday]=DATE(2018,9,3), [Holiday]=DATE(YEAR,MONTH,DAY), [Holiday]=DATE(YEAR,MONTH,DAY)),0.5,0)
    – mdevm
    Jul 16, 2018 at 19:05
  • IT says that, column maybe does not exist or there are syntactical errors.
    – mdevm
    Jul 16, 2018 at 19:05
  • You had mentioned you needed 4 dates, so I left room for you to enter those here: [Holiday]=DATE(YEAR,MONTH,DAY), [Holiday]=DATE(YEAR,MONTH,DAY). If you only have 2 valid dates, you only need the first two parts here: [Holiday]=DATE(2018,10,8), [Holiday]=DATE(2018,9,3). If not, you will have to change the "YEAR, MONTH, DAY" to the actual values you want to accept. In my formula, they are placeholders for the other two dates you didn't mention. Hope this helps.
    – C. Dennett
    Jul 16, 2018 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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