An alternative way to convert a date?

[alexis]

In my data set I appear to be receiving a date in an unusual format. It's numeric (e.g. 1355833734) and it turns out that it's the number of seconds since 1/1/1970.

Below is one way of converting it:

ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + (call_date / 86400)), 'Athens') as CallDate

Are you aware of a better way that makes it less "regional"

Thanks in advance

Alexis

[jagan]

Hi,

Try like this

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)))

Regards,

Jagan.

[jagan]

Hi,

Try like this

=Time(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'MM/DD/YYYY HH:MM:SS')

Regards,

Jaga

[alexis]

Thanks for the reply Jagan - this second reply of yours gave me:

12/30/1899 12:12:54

which is incorrect - it should be a date in December 2012!

I need to take the original long number and create:

a) Date in the format dd/mm/yyyy (exactly as my local settings dictate)

b) Time in HH:MM:SS format..

[jagan · Accepted solution]

Hi,

Try this

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'M/D/YYYY h:m:s')

Regards,

Jagan.

[alexis]

Thanx Jagan,

So I guess with:

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'D/M/YYYY')

So the "integer" representation of the date will then be something like:

=Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400),'##0')

Thanks for your help

Alexis

[CELAMBARASAN]

Hi,

I guess you need to subtract one day. because as per your statement it starts from 1/1/1970. here you have included that day too.

=Date(Num(Date(MakeDate(1969, 12, 31)) + (1355833734 / 86400)), 'D/M/YYYY')

OR

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400) - 1), 'D/M/YYYY')

Hope that helps

[jagan]

Hi Alexis,

The integer representation of date is

=Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400))

Hope this helps you.

Regards,

Jagan.

[alexis]

That is not correct Jagan - it gives a decimal value not integer..

[jagan]

Hi,

Try this

=Floor(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400))

Regards,

Jagan.