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)))
makilaAsked:
Who is Participating?
 
mbizupCommented:
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.
0
 
mbizupCommented:
>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")
0
 
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...
0
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.

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

TimeSerial(h,m,s)

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.
0
 
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?
0
 
mbizupCommented:
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:
iif(expression,truepart,falsepart)

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.
0
 
mbizupCommented:
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], ""...
0
 
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 :)
0
 
makilaAuthor Commented:
Nope, Access 97 in Design view. I put the formula in the Field field.
0
 
makilaAuthor Commented:
Thanks for everything!!! That all makes sense. I upped the points since I asked you a zillion questions. Have a great night!
0
 
mbizupCommented:
Thanks and good luck with your project!
0
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.