Convert Standard Date to Number of 100-Nanosecond Intervals Since Jan 1, 1601

[TheAssetMgr]

I need a formula to change a standard date (ex: 6/1/2019) to the format Active Directory can use.

The problem is that AD stores date values as the number of 100-nanosecond intervals that have elapsed since the zero hour of Jan 1, 1601. I've found several places that tell me to use this formula to convert TO a standard date format but I can't find anything for the reverse: =IF(C2>0,C2/(8.64*10^11)-109205,"")

Can anyone tell me how I can convert a value of 6/1/2019 in cell A1 to the number of 100-nansecond intervals since 1/1/1601?

Thanks!

 

[steve the fish]

If you first formula works then this should:

=8.64*10^11*(A1+109205)

 

[TheAssetMgr]

If you first formula works then this should:

=8.64*10^11*(A1+109205)

Thanks for the quick response! This almost works perfectly - hopefully it can be easily tweaked.

When I converted 6/1/2019 I received the following value: [TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl66, width: 154"][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl66, width: 154, align: right"]132038208000000000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

When that value was imported into AD the result was 5/30/19. Any thoughts on what to change to add the additional two days?

Thanks

 

[TheAssetMgr]

Actually, your formula may be working just fine. I went to https://www.epochconverter.com/ldap and dropped in the long format value and the website said it was for 6/1/19. It said though that it was in GMT and I'm on the east coast of the US. That still doesn't explain why there was a two day difference. I could understand where a time zone issue would cause a one-day difference, but not a two-day.

Not sure if that helps you at all.