Time Formula - How does it work?

Can anybody tell me exactly what this formula is doing? I think the 1/1/1970 date is due to the server thinking that's the beginning of time.

All I know is that it turns a number into a time but I want to know what it's doing to achieve that result. I also want to change it so it doesn't result in a field for the seconds.

For example:
login = 1143093610
row_date = 03/23/2006
results in "6:00:10 AM"

LoginTime: IIf([login] Is Null,Null,TimeSerial(0,Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60),([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))-(Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60)*60)))
Who is Participating?
Okay... I'm back

Access 97 would explain it.  I'm using 2003..

I think the lack of  Hours just saves an unnecessary calculation.

as far as I can tell this is what is happening:
>Minutes = Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60)
The login time is in seconds.
The Row_date-1/1/1970 is converted to seconds so that it can be subtracted from the Login time.
Then all of that is converted to minutes (/60) ... which is then converted to integer to work with the timeserial function.

This part is giving me a headache looking at it!  Basically the same thing, but in seconds (although it looks like there is a conversion to minutes followed by a conversion to seconds?  (if it aint broke, dont fix it...)
>Seconds =  ([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))-(Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60)*60))

I'm really guessing here, but I think that doing this all in seconds would result in an overflow, and calculating hours is simply not needed because the dateserial function chugs this out in the right format.
>I think the 1/1/1970 date is due to the server thinking that's the beginning of time.
Cool!  That makes me prehistoric!

To get the time in the format you want:

LoginTime: Format(IIf([login] Is Null,Null,TimeSerial(0,Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60),([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))-(Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60)*60))),"hh:nn AM/PM")
makilaAuthor Commented:
Thanks! That worked great. Are you able to tell me how the formula is converting 1143093610 into 6:00:10 AM? I want to understand what it's doing inside the TimeSerial function...
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

The timeserial function's syntax in it's most basic form is like this:


it will take variant (or integer) values of h,m, and s and combine them into a regular looking time.
The rest of that is simply computinig what goes into h,m and s.
makilaAuthor Commented:
So then:

Hour = 0

Minutes = Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60)

Seconds =  ([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))-(Int(([login]-((DateValue([row_date])-DateValue("1/1/1970"))*24*60*60))/60)*60))


What does "Int" do?
Int will change the datatype of the given expression to integer (btw, what is the data type of row_date in your application?)

Then the iif statement does this:

if your code, that is

iif (login is Null, Null, Your time computation)

so if you have a null value for login, null will be returned.  Otherwise the computed time will be returned.
Another question... Are you doing this in access VBA??

I'm curious because I tested this in Access and wound up with an error because of this part:

IIf([login] Is Null,Null...

works fine like this:

IIf (IsNull [Login], ""...
makilaAuthor Commented:
row_date is a date in m/d/yyyy format

any idea why the hour is set to 0 in the TimeSerial function?

sorry for so many questions, this is a formula i didn't create, obviously :)
makilaAuthor Commented:
Nope, Access 97 in Design view. I put the formula in the Field field.
makilaAuthor Commented:
Thanks for everything!!! That all makes sense. I upped the points since I asked you a zillion questions. Have a great night!
Thanks and good luck with your project!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.