Time question, 6 years still no answer....

[jaad]

There are literally thousands and thousands of entry about this subject but still after 6 years of looking and researching I haven't found a simple answer to a really simple question:

I have two fields: [time start], [time end] format as am and pm and a mask that validate the field as a short time mask.

What I need to know is how to calculate the difference in hours and in minutes?

6 years after I still can't do it! Everybody that wants to help gives me a datediff function that never works. it either gives me a bunch of number that I can't make any sense of or it only gives me hours but no minutes.

I tried formating my field so they are all alike but that doesn't work either.
I tried using "hh:nn:ss" still no go!
I tried using the datediff with a format function, nada!
I tried simply [time end]-[time start] = bunch of number and a E in there too. NO idea what that is.

In all, I have had people sending me codes, complicate formulas, and all sort of things to still having to use my desktop calculator to input the difference between the two field after 6 years.

Is there anyone, I mean anyone on Earth or beyond that can answer this simple question??????? ??????????????? ??????????????? ??????????????? ??

[ChipR]

First, do you have a date field or a text field? I'm not sure exactly what you mean when you say "format as am and pm".

[jaad]

Forgive me I will expand:

I have a table in which I keep my time spent for jobs. It is called [task detail]

I enter my starting time for a job in the field [start time] which is on a form. the format of the time is 24 hours. when I type in 23:00 it basically returns me 11pm.

I do the same thing with my end time and it is formated the same way.

Now,,,, I can have a unbound field in form that has [end time]-[start time] and it returns me the correct time spent on a job that day.

but when I want to create a query to produce a report out of the table [task details] where my time is kept I can't get Access to calculate the difference between the two time [start time] and [end time]

i either get Error messages; a bunch of numbers that make no sense; zero, or just the hour, but if is is under one hour I get zero.

[missinglinq]

First, do you have a date field or a text field? I'm not sure exactly what you mean when you say "format as am and pm".

This question has been solved, literally hundreds of times, given the needed facts! You haven't answered Chip's questions, which are vital to us helping you! Are we talking about fields defined as Date/Time or are we talking about Text fields?

Also, are you simply entering times, or dates/times?

Lastly, it really makes little sense that you can have a control on a form display the difference in you start time/end time, but you can't have a control on your reports doing the same thing. Can you upload your database?

Linq ;0)>

[jaad]

both fields are defined as date/time........... ........... and formated to show time in this format (when I type in 23:00, when I change field the field shows 11pm. I said that earlier. it is time, just time. no date no text. I have an input mask that I set so there can't be any mistakes. I type in 23:00 and it turns into 11pm. I really don't know how else to explain this???????? its not a text field nor a number nor a memo nor yes or no, not an attachment nothing, just time.

I have an unbound field in my form that automatically tell me the difference between [start time] and [end time] that works perfectly, I have no problem with that. the problem is that it is showing only on form and every time I need to print a report I have to enter each day by end using my calculator.

I tried a million different things......... . I been at it for 6 years since I had Access 2003. I am using access 2007 now and I still have the same problem. I never been able to have the darn time calculated. I often thought that most people on the planet use this program to store financial numbers and the next thing after that comes to mind has to be with date and time.

Why is it so hard to get any sort of automatic function like you could have with number and text. why is date and time so backward and complicated?

Sorry for venting out,if anyone of you had had that problem for as many years as I have i think you would all feel the same way.

Final note: Why every time I ask a question about "time" people automatically assume that I am talking about "dates"? forgive my ignorance and pardon my French English is not my first language.

[missinglinq]

Because "time" does not exist as an entity unto itself! "Time" is always part of a date!

Since you've been researching this for six years without success, I suspect you need to actually post a copy of your database. There's no way that [time end]-[time start] is going to give you a correct answer.

Help us to help your.

Linq ;0)>

[jaad]

LOL well it works! I don't know how it is possible that it works if it is impossible that it could but it does. the control source of my unbound field is written as such:

=[time end]-[time start] and it gives me exactly the right amount of time I spent on a job every time. the format for this unbound field is set to "Short time." Hey! who knows? maybe I am showing a new thing nobody knew before. might have been too simple of a thing to do for anyone to think about it??? I don't know? I jsut know it works on my form.

I will leave it at that. I appreciate you guys wanting to try to help me, truly do. thanks

I unfortunately can't upload my database because the information included within is privileged unfortunately.

[neocambell]

Here is the solution

Hi,

It is quite unfortunate that you spent such a long time to find a solution. I was using this since the day I started databases about 12 years back ;o)

But this world is sometimes like that. People keep things as secrets. However "sharing knowledge is the path to wisdom" is what I believe just as the guys at Bytes.com.

Have a look at the following link.
** Link removed as per site rules **
^ This link was removed last time.. but the solution is there ;o)

With Best Regards,

Neo

[mdommer1]

Here's a fun way to do it:

Function ShowDuration(StartTime As Date, EndTime As Date) As String   'added 1/23/08 by MJD
 
    Dim lngSeconds As Long
    Dim strDuration As String
    Dim FoundIntValue As Long
    
    strDuration = ""
    lngSeconds = CDbl(EndTime - StartTime) * 60 * 24 * 60
    
    If lngSeconds = 0 Then
        strDuration = "0 seconds"
    Else
        ' years ' 1 year = 31,556,926 seconds
        If lngSeconds >= 31556926 Then
          FoundIntValue = Int(lngSeconds / 31556926)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 year"
          Else
            strDuration = strDuration & " " & FoundIntValue & " years"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 31556926
        End If
        
        ' months ' 1 month = 2,629,744 seconds
        If lngSeconds >= 2629744 Then
          FoundIntValue = Int(lngSeconds / 2629744)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 month"
          Else
            strDuration = strDuration & " " & FoundIntValue & " months"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 2629744
        End If
        
        ' weeks ' 1 week = 604,800 seconds
        If lngSeconds >= 604800 Then
          FoundIntValue = Int(lngSeconds / 604800)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 week"
          Else
            strDuration = strDuration & " " & FoundIntValue & " weeks"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 604800
        End If
        
        ' days ' 1 day = 86,400 seconds
        If lngSeconds >= 86400 Then
          FoundIntValue = Int(lngSeconds / 86400)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 day"
          Else
            strDuration = strDuration & " " & FoundIntValue & " days"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 86400
        End If
        
        ' hours ' 1 hour = 3,600 seconds
        If lngSeconds >= 3600 Then
          FoundIntValue = Int(lngSeconds / 3600)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 hour"
          Else
            strDuration = strDuration & " " & FoundIntValue & " hours"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 3600
        End If
        
        ' minutes ' 1 minute = 60 seconds
        If lngSeconds >= 60 Then
          FoundIntValue = Int(lngSeconds / 60)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 minute"
          Else
            strDuration = strDuration & " " & FoundIntValue & " minutes"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 60
        End If
        
        ' seconds
        If lngSeconds >= 1 Then
          FoundIntValue = lngSeconds
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 second"
          Else
            strDuration = strDuration & " " & FoundIntValue & " seconds"
          End If
        End If
    End If
    
    ShowDuration = Trim(strDuration)
    
End Function

[mdommer1]

BTW, the above post was not really intended to measure time precisely beyond the WEEK unit due to the variations in length of month and year. But it was an entertaining attempt to mimick the Time Remaining messages seen in so many places.

[mshmyob]

Not sure what the big deal is.

I just setup a sample like you claim you have.

I have a table with 2 fields (StartTime and EndTime), both set as Date/Time and formatted as Medium Time.

I enter say 23:00 as the start time and 23:24 as end time.

On my form I have 3 text boxes (no formats applied). Form is bound to the table that has the times entered. First 2 show me my times from my table and they display as 11:00 pm and 11:23 pm respectively.

Third text box control source is

[code=vb]
=DateDiff("n",[StartTime],[EndTime])
[/code]

Result is 24 - ie: 24 minutes time difference between the two times.

This can be applied to the report just as easily.

But since you refuse to show us your database or even the settings for your report it is impossible to help any further.

cheers,

[jaad]

how do you get it to show both "hour" and "minutes"? I can't find any documentation anywhere?

[NeoPa]

It's hard to understand why you seem to have such difficulty grasping this. This isn't a problem that's stumped the whole world for ages. It may have stumped you for a while but having seen all the suggestions I'm having a really hard time understanding why.

I ran the following code in the Immediate Pane :

StartTime=Now()
'Wait a period of time before executing the next line.
?Format(Now()-StartTime,"hh:nn:ss")

The result I got was :

00:01:13

I could have waited longer. I could have restricted the display such that it ignored the seconds. I could have done a lot of things with it, but I think it illustrates clearly how straightforward the solution is.

If there's anything about the demonstration, or the code, or anything, that you don't follow, please explain specifically what it is and I will endeavour to clarify it further.

[mshmyob]

how do you get it to show both "hour" and "minutes"? I can't find any documentation anywhere?

Simple math. Change the control source I showed you above to:

[code=vb]
=Round(DateDiff ("n",[StartTime],[EndTime])/60,0) & ":" & Round(DateDiff( "n",[StartTime],[EndTime]) Mod 60,0)
[/code]


If StartTime is 22:00 and EndTime is 23:24 then the result will be 1:24 indicating 1 hour 24 minutes. You could of course put in text to display hours and minutes instead of the ":" character.

cheers,

[jaad]

I wrote the line exactly like you wrote it and it still doesn't work. It is asking me for a "parameter" now?????