Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try this
=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'M/D/YYYY h:m:s')
Regards,
Jagan.
Hi,
Try like this
=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)))
Regards,
Jagan.
Hi,
Try like this
=Time(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'MM/DD/YYYY HH:MM:SS')
Regards,
Jaga
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..
Hi,
Try this
=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'M/D/YYYY h:m:s')
Regards,
Jagan.
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
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
Hi Alexis,
The integer representation of date is
=Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400))
Hope this helps you.
Regards,
Jagan.
That is not correct Jagan - it gives a decimal value not integer..
Hi,
Try this
=Floor(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400))
Regards,
Jagan.