Opposite of DateAdd

[Rasm]

I have a Long Integer number that represents the number of seconds since Jan 1st 1980 - so I use the DateAdd command to convert it into a date - I then use a routine by Chip Pearson to convert that date from Greenich (GMT) to local time.

This same Chip Pearson routine allow me to convert a Local time to GMT time - so now that I have the GMT time - I want to convert it to a long Integer representing the number of seconds since Jan 1st 1980 - But I cannot find that command.

Any help is appreciated.

[Excel Fox]

Shouldn't it be as simple as multiplying with 24*60*60? Unless I am mistaken, a date for example 10/26/2011 7:17 will give a value of 40842.30411 which can be multiplied with 24*60*60 to give 3528775076 (approximately)

If this is not what you are looking for, can you post the original code? Maybe we can try reversing it.

[Rasm]

HeHe - I wish - the problem is that you have leap years and also you even have what is called leap seconds. So I defintely need to find the command that does this. So for me to write my own code is no good - I was just hoping somebody knew the command.

Here is an articvle that discuss the problem http://mathforum.org/library/drmath/view/61035.html

- so I calculate the long integer to represent not only the date - but also the hour, minute and seconds. So Not only do I have leap years - I also have what is calleed leap seconds.

The code is shown below - where AAlong is the number of seconds since Jan 1 1980 - and Avar is a variant or date variable.


Avar = DateAdd("s", AAlng, #1/1/1980#) 'Greenich time


So now I want to take a date/time (i.e. todays date/trime) stamp and convert it to seconds since Jan 1st 1980'

[Excel Fox]

Rasm, I do not intend to question the sanctity of the theories in that site, however, I know for sure that if you convert a date time value in Excel to its value, the number returned will be equivalent to the number of days starting from 1/1/1900 0:00:00, inclusive of all leap years and leap seconds.

So based on my theory, 10/27/2011 12:01:51 AM will be converted to 40843.00128 (days) which means 40843.00128*3600*24 seconds = 3528835311 seconds

Now, if you reverse this to date, using a simple text function like so =TEXT(D1,"m/d/yyyy h:mm:ss AM/PM"), you would get the original date 10/27/2011 12:01:51 AM

If you are still confused, I can prove it with an attachment :)

[Excel Fox]

With due respect to the math forum, I have to point out that the question there isn't dealing with an application like Excel. For a normal (read manual with no Excel) calculation, one would have to take in to account all those calculation of leaps. So with no credit taken away from there, you can safely consider the above post as a viable solution to what you are looking for.

[Rasm]

You are absolutely correct - however I am reading my date stamps as long integers from a Binary file - using random access -- along with a bunch more info - next I manipulate my data in Excel - next I write back to the binary file - But now have to leave a date/time stamp of my modification. So this is where I need to convert the Date/time stamp into a long integer. The binary files are originally written by C++ code.

So the example you shown me above - I will use that - all I have to do is subtract an offset in seconds to represent thetime period 1900 to 1980.

So this I will test - thank you very much.