Help with Date Formatting

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

Best Grad Date Formula.PNG

Browse tool after formula showing where it sometimes works and sometimes doesn't: Formula Mostly Working.PNG

My regexes:

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

REgexes mostly working.PNG

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!

Transformation

Accepted answers

binu_acs

@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

Graduate_080823.yxzp

All comments

binu_acs

@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!!!

Sample App Report.xlsx

Sample grad report.xlsx

Sample progression report.xlsx

Sample Tracker Report.xlsx

binu_acs

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

Graduate_080823.yxzp

Graduate_080823.yxmd

katherinetdavita

@binu_acs 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.

binu_acs

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

Let me know the attached workflow works for you or not

Graduate_080823.yxzp

katherinetdavita

This is helpful, thanks.

sireeshagandam

Good

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703