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:
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.
Absolutely! Thank you for responding!
I set a "Run If" action after this however upon re-evaluating, I see my logic is off.
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!
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,
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!