Unable to convert account expiry column to date in active directory

[stadepalli]

I am trying to convert the account expiry column in active directory to date format. But if the value goes to milli seconds, conversion throws error.

Example value: 9223372036854775807

[Anonymous]

Hi @stadepalli ,

Could you kindly provide some sample data from the table that includes the account expiry column with Text format, along with the expected result based on the shared sample data? Additionally, please explain the logic or conditions for converting the date format. This information will help us better understand the issue and provide an accurate solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[stadepalli]

Hi, I could convert the values to date time. I just crsoo checked in AD for errored values and they seems to be null. So just converted the correct ones. But after converting the hexadecimal number directly to date time format, I am seeing a difference in the actual date and converted date.

Example: 133801812000000000

When i convert this to date: its shows: 1/1/2025 10:30:00 AM +05:30

Whereas, the actual date is 6/23/2024 20:00

Even if I consider the timezone, then also there's a 6 months difference.

[rajendraongole1]

Hi @stadepalli - Can you try the below logic in your table

AccountExpiryDate =
IF (
[accountExpires] = BLANK() || [accountExpires] = 0,
BLANK(),
DATE(1601, 1, 1) + ([accountExpires] / 864000000000)
)

Hope it helps , othereise please share the pbix file with sample data we will analyze and share it.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[stadepalli]

Hi @rajendraongole1 ,

It's still the same. 

1/1/2025 5:00:00 AM

[Anonymous]

Hi @stadepalli ,

Did you get the solution for your problem? If no, please check if CONVERT function can help you get the expected result...

Best Regards

[stadepalli]

I am getting below error:

[rajendraongole1]

Hi @stadepalli - create a calculated column as below

Expiry Date =

IF(

    VALUE([AccountExpiry]) = 9223372036854775807,

    BLANK(),

    DATE(1970, 1, 1) + (VALUE([AccountExpiry]) / 864000000000)

)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!