Help with Date Formatting

[katherinetdavita]

Hello Community!

It's me again. I am working on a project related to education program data and have several data sources coming together that each contain a field that is essentially a graduation date - its not always populated on each source and sometimes the data sources have conflicting date, so I have written an if formula to create what I'm calling (for now) "Best Grad Date" based on a hierarchy I determined for which date, from which source, if most reliable.

Later in the workflow I need to do some additional things with that date, like calculating the difference between 2 dates (time from selection to graduation, time from graduation to termination if applicable) and marking another field as graduated Y/N, etc. So after that IF formula, I've got a series of regexes to format all the different date formats that newly created "Best Grad Date" field to one consistent formatting.

That said, I'm having a couple problems:

1. My IF formula for "Best Grad Date" is not working correctly for all dates that are incoming.

2. I'm not sure my regexes are working or if it will work for what I need to do with that field later in the workflow.

Here are some screenshots of the issue:

Formula for Best Grad Date

Browse tool after formula showing where it sometimes works and sometimes doesn't:

My regexes:

Browse at the end of these regexes where sometime it works and sometimes it doesn't:

 

Thanks in advance for all help - i thought this would be simple but I'm frustrated and running out of time on projects related to this one little date!

[binuacs]

@katherinetdavita would you be able to provide a sample input file and expected output result?

[katherinetdavita]

Here are pared down samples of each of the inputs. I think I'm seeing that its the Graduation report that is not working correctly with the formulas.

As far as the output, there's a gazillion other things I need to do with the data, but basically what I need is for the format of the Best Grad Date to be consistent regardless of where the date cam from and for me to be able to use it later in the workflow to do math on compared to other dates as mentioned in my original post.

Thank you!!!

[binuacs]

@katherinetdavita Attaching a sample workflow, in which you can make changes according to your requirement

[katherinetdavita]

@binuacs thanks - I may use parts of this but I think you've got your workflow set up to find the latest/max grad date as the best graduation date with that summarize tool. Although it may look like it from the sample data, it's not always the max date that is best. What I've got is multiple data sources in which some are more reliable so I'm trying to accomplish saying essentially "if there's a confirmed grad date from the tracker use that, but if that's blank, use the one from the tracker, if those two are blank, use the one from the application report and if those three are blank, use the graduation report, otherwise make that field null.

Can you tell me more about what that Multi-Field Formula tool is doing? That one's new to me.

[binuacs · Accepted solution]

@katherinetdavita If you want to specifically mention the date you can update the formula like below

The multi-field tool allows you to update more than one field at the same time. Also it has the features to change the data type according to our wish by keeping the same field name. For eg: The Best Grade Date is a string field, using the Multi-Field tool I changed the data type to Date and kept the same name as Best Grade Date. The normal formula wont allow that, if you want to change the data type you need to create a new field.

Let me know the attached workflow works for you or not

[katherinetdavita]

This is helpful, thanks.

[sireeshagandam]

Good