Date-Time to Number

[jnewland1234]

I need to convert 1/3/1900 12:00:00 AM to 72.00 through alteryx. See attached file for examples in excel. 

[kat · Accepted solution]

Hi @jnewland1234

Hope the attached is what you needed!

Kind regards

Katrin

[jnewland1234]

This worked, but I might be back with another question. 

[danilang]

Kudos to you @kat for deciphering the algorithm behind that.  How did you figure that one out?

Dan

[kat]

Thanks @danilang :) a little bit of playing around with the dates in Excel did the trick

@jnewland1234 - could you perhaps just mark the solution as accepted so that others know that it is resolved :)

[jnewland1234]

I accepted, but I am still having trouble with my database. Mainly because my data is coming in, through my macro, as a string so when I go to convert to date/time it ignores anything under 24 hours. 

[kat]

@jnewland1234 - could you then perhaps first convert it to datetime format with the datetime tool before doing the conversion steps?

[jnewland1234]

I tried. The problem is that the data that is coming in is in String so when I try to convert to Date/Time using select the under 24 hours time defaults to Null. I think I have almost figured out how to convert but it's somewhat complicated and I have my fingers crossed. Do you have a formula that would convert string to date/time format? 

[kat]

@jnewland1234 - try the date time tool under the parse section of tools. This allows you to convert string to date/time without loosing anything