How to change excel column format before input?

Question

I have an excel doc with a column that is in DATE format in excel. The column has dates in some cells, but in other cells of the same column it has text.

When I use the input tool to bring this file into the workflow it comes in assuming that all cells are dates.

Cells that had a date in excel, come in with a date in the workflow. Cells that had a text string in excel come in to the workflow as 00:00:00.

I know there's a way to change formatting in the workflow using the Select tool after the data is already in the workflow, but is there a way to change it at the same time as the input?

As a test I tried changing the column in excel to General before starting the workflow. This works, and the text comes in as it should, but the dates are converted to excel serial format  (44133).

LBCoEnterprises · Answer

@Maskell_Rascal

This solution didn't work for me, but I may have figured out an answer as to why.

I went back and look at the column in question in excel, and the data that's there is excel formulas.

Once I converted to the data from formulas to just static data, then the workflow brings in the column correctly as expected.

Maskell_Rascal · Answer

Hey @LBCoEnterprises

I got thinking about your problem a bit more, and wanted to create something a bit more dynamic to account for the column having both dates and strings.

This workflow accounts for both, converts your dates accordingly, and then give you a final mixed output. Keep in mind that the dates are stored as strings in the final output, since there is text in the column.

I've attached a copy of the workflow for you.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Convert Date and String Column from Excel.yxmd

atcodedog05 · Answer

@Maskell_Rascal

Interesting function

Thanks, Got to learn something new 🙂