codyhayes12
Scholar

Calculate Date from Calculated Column

I am attempting to calculate a date in my workflow based off the results of a calculated column in my list. I have a script that takes the choice from my "Duration of Project" field (15, 30, 60, or 90 days) and then creates a date that reflects each duration. I use "Calculate Date" set the value to "Expiration Date" and subtracted 10 days. I set this to a variable, varReminderDate.

My end goal is to send a reminder email 10 days prior to that expiration date. However, the calculate date action fails. I receive this error: 

Error calculating date. The added or subtracted value results in an un-representable DateTime.
Parameter name: value

Is it even possible to do what I am attempting? I have tried this as a site workflow but my log shows the dates return as 1/1/000112:00:00 AM and 1/1/1900 12:00 AM. It then pauses until 1/1/0001 and sends.

 

0 Kudos
Reply
4 Replies
Michel
Contributor

Re: Calculate Date from Calculated Column

Hi,

So the calculation of the Expiration Date works, but retracting 10 days from that day to create a Reminder date fails? Can you add a screenshot of the configured 'Calculate Date'-action?
0 Kudos
Reply
codyhayes12
Scholar

Re: Calculate Date from Calculated Column

Absolutely! Thank you for responding!

CalDate Snip.PNG

I set a "Run If" action after this however upon re-evaluating, I see my logic is off.

run if snip.PNG

How can I tell the workflow to wait to run until the calculated date runs? I attempted this in a site workflow with a pause action but the variables showed up as 1/1/0001 for the date. Thanks!

0 Kudos
Reply
gman
Apprentice

Re: Calculate Date from Calculated Column

cody,

 

The 0001 dates you're seeing are the result of an empty/null date variable or field. A null date variable returns one format and a null date field returns another. I forget which is which.

 

My suggestion is to set all you dates into variables at the start of the workflow. So you're reading from the item's date field and storing that in variable. However, don't use the Set Variable action. Use the calculate date action as you've done.

I would do this for the Expiration date also. Once you've got both in variables, write those results to the workflow log and check the results.

You can't simply test a date for "empty", so we check valid dates by determining if their greater than 1/1/1950 or something similar.

 

Another issue is that you can't successfully compare dates and expect them to be equal unless you've formatted them accordingly. I know, its a tedious process. SharePoints date carry the time with them, even if the field is setup to not display it. If the time is used, you'll never have equivalent date/times. To get just the dates, you'll need to use Build String action and the FomatDate inline function saved to a text variable. From there you can compare them as only dates.

 

Hope this helps,

Gman

0 Kudos
Reply
codyhayes12
Scholar

Re: Calculate Date from Calculated Column

gman,

Thank you for the insight! I had previously though to do this, however the complexity of the list I am working from has caused me to question how I would go about configuring what you have suggested.

 

We have a field called "Duration" for the end user that allows them to select from 4 choices (15 Days, 30 Days, 60 Days, 90 Days). The expiration date is based off that choice. Is it possible to configure my workflow to set the expiration date after the item has been created? Would I need to set 4 different calculate date actions to configure each possibility into a variable?

 

I see exactly what you are looking to do, if I can get the expiration date into a variable, the workflow will for sure have the proper date and then I can use another calculate date action to set the reminder, and then have it pause or run based on that date. At this point my workflow is extremely large from approvals and pulling in approver comments etc, I am wondering where I can set these particular actions so as not to interrupt the required approval actions.

 

I have attached a screenshot of my approval workflow and maximized the two different repeating actions sets, one for sending a notification after an individual approval and one for gathering comments which are then input into an empty field. Let me know a good spot you think I could configure the calculate date actions without causing interference with an approval action, thanks!

0 Kudos
Reply