Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

datetime field to number

EleniTzakopoulou
5 - Atom

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.

 

 

6 REPLIES 6
ChrisTX
15 - Aurora

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
14 - Magnetar

Can you please  attach some sample input file?

apathetichell
18 - Pollux

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
5 - Atom

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

EleniTzakopoulou
5 - Atom

I attach the input file

Hammad_Rashid
11 - Bolide

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.

Labels