Expand my Community achievements bar.

SOLVED

BAAAM tool export date format

Avatar

Level 1

Hi, am trying to read the created and update date fields on the BAAAM export from AAM but the data which is available can't be transformed into any dates which Excel recognises, anyone know how to convert it? Thx

1 Accepted Solution

Avatar

Correct answer by
Level 3

I use the formula below (and then format that cell as a date)

 

=A2/(86400000)+DATE(1970,1,1) 

 

(where A2 contains the UNIX timestamp you are trying to convert)

 

 

View solution in original post

2 Replies

Avatar

Level 1

Hi @richlodge5 

 

Here is the BAAAM Date formula: =((A2/1000)/86400)+(DATEVALUE("1-1-1970")-DATEVALUE("1-1-1900"))

Make sure you replace the 'A2' to reference the the date cell you are trying to change. Also make sure that the raw (reference) date column (in this example 'A2') is formatted as general

The new date column will need to be formatted as 'Custom' and then you should make it: m:dd:yyyy AM/PM.  

 

It can get a bit wonky sometimes so if anyone else has a better way please share.

 

Thanks,

Manny

 

Avatar

Correct answer by
Level 3

I use the formula below (and then format that cell as a date)

 

=A2/(86400000)+DATE(1970,1,1) 

 

(where A2 contains the UNIX timestamp you are trying to convert)