asked on

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)))

mbizup

>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")

makila

ASKER

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...

mbizup

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.

makila

ASKER

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?

mbizup

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.

mbizup

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], ""...

makila

ASKER

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 :)

makila

ASKER

Nope, Access 97 in Design view. I put the formula in the Field field.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

makila

ASKER

Thanks for everything!!! That all makes sense. I upped the points since I asked you a zillion questions. Have a great night!

mbizup

Thanks and good luck with your project!