We help IT Professionals succeed at work.

# Time Formula - How does it work?

on
Medium Priority
1,522 Views
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:
row_date = 03/23/2006
results in "6:00:10 AM"

Comment
Watch Question

## View Solution Only

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:

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.

Commented:
So then:

Hour = 0

???

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)

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:

works fine like this:

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

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

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.

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!
##### Thanks for using Experts Exchange.

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