[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1505
  • Last Modified:

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)))
0
makila
Asked:
makila
  • 6
  • 5
1 Solution
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now