datetime field to number

[EleniTzakopoulou]

Could you help me to convert this datetime field to number?

The input file is like the input file attached and the tool gave me a datetime field as the input tool attached.

[ChrisTX]

For an Excel input, try using the File Format Microsoft Excel Legacy

For the File Format drop-down, my understanding is:

Microsoft Excel is a driver created and maintained by Alteryx

Microsoft Excel Legacy is a driver created and maintained by Microsoft

Sometimes the two drivers produce different results

If that doesn't work, use the option First Row Contains Data

Then you'll get each field as a String and will need to change data types as needed.  And use the Dynamic Rename tool to get the correct field names.

Chris

[Raj]

Can you please  attach some sample input file?

[apathetichell]

Your easiest way to fix this is to turn the column into numbers in excel. right now the column is a date in excel (I know - it looks like a number). so alteryx is interpreting it as a date (and the result is a messy time add to 1899-12-31.)

[EleniTzakopoulou]

Unfortunately we are not allowed to edit the input but thank you for your answer anyway!

[EleniTzakopoulou]

I attach the input file

[Hammad_Rashid · Accepted solution]

It looks like you want to convert a datetime field to a number in Alteryx.

You can follow these steps:

1. Drag and Drop Input Tool:

   • Start with an Input tool to read your data.

2. Select the Datetime Field:

   • Use a Select tool to choose the datetime field you want to convert.

3. DateTime to String:

   • Use a DateTime to String tool to convert your datetime field to a string in a format suitable for conversion to a number. Choose the appropriate format.

4. Formula Tool:

   • Use a Formula tool to convert the string to a number. You can use the ToNumber() function in Alteryx.

5. Output Tool:

   • Finally, connect an Output tool to save or analyze the data with the newly converted number.

Here's an example formula you might use in the Formula tool:

ToNumber(DateTimeToString([YourDateTimeField], "%Y%m%d%H%M%S"))

Replace [YourDateTimeField] with the actual name of your datetime field.

This formula converts the datetime field to a string in the format "YYYYMMDDHHMMSS" and then uses ToNumber() to convert it to a numeric value.