We help IT Professionals succeed at work.

Time Formula - How does it work?

makila
makila asked
on
Medium Priority
1,522 Views
Last Modified: 2012-08-13
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)))
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

Author

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...
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

Author

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

Author

Commented:
Nope, Access 97 in Design view. I put the formula in the Field field.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for everything!!! That all makes sense. I upped the points since I asked you a zillion questions. Have a great night!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Thanks and good luck with your project!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.