Calculating time differences with Hours and Seconds

[LisaJill]

Hi...

I have, at home, a database t hat is a journal. It has multiple purposes - track hours at an internship, as well as to keep a journal of activities.

I'm having some difficulties with one of the functions. I did a search here and found out how to calculate the difference in Time fields

So, in my form for entering hours/dates I added an unbound text box, and put this in the control source:

=DateDiff("h",[fldtimestarted],[fldtimeended])

The fields that it is calculating are Date/Time fields - Formatted in Short time. I work 9-5 and willl NEVER work overnight. I store the time in 24 hour format.

This works great except when I have partial hours. So today I worked from 14:00 to 17:30 But it says that I worked 3 hours, rather than 3.5 hours.

I tried to do this:

=DateDiff("h",[fldtimestarted],[fldtimeended]*24)

And it gave me some very very strange numbers (like -831.99999)

so I tried:

=DateDiff("n",[fldtimestarted],[fldtimeended] / 60)

and once again got some very strange numbers.

Can someone help me out with this? I want to keep the Date and Time fields seperate due to differing requirements at the place I work versus the college - since I'll never work overnight I was hoping that wouldn't be an issue...

Thank you again for all your help =)

I can zip/attach the database if that would help, just let me know.

 

[EMP]

Try this.

=DatePart("h",[fldtimeended]-[fldtimestarted])+DatePart("n",[fldtimeended]-[fldtimestarted])/60

 

[LisaJill]

That worked beautifull, thank you.

Also.. how can I manipulate this? Even if someone could piont me to a helpfile? I need to be able to sum this field up so I know how much I worked a month.. or since I started.. or whatever dates are requested. If i was storing it I would know how to reference it but since its calculated on the fly.. I have no idea how to manipulate it.

thanks =)

 

[EMP]

The expression to use in a query is:-

Sum(DatePart("h",[fldtimeended]-[fldtimestarted])+DatePart("n",[fldtimeended]-[fldtimestarted])/60)


For example, to get the monthly total, you can run a query like this:-

SELECT Format([TableName].[DateFieldName],"yyyy / mm") AS [Month],
Sum(DatePart("h",[fldtimeended]-[fldtimestarted])+DatePart("n",[fldtimeended]-[fldtimestarted])/60) AS [TotalHours]
FROM [TableName]
GROUP BY Format([TableName].[DateFieldName],"yyyy / mm");


Edit:-
Your original expression:-
=DateDiff("n",[fldtimestarted],[fldtimeended] / 60)

should work when the closing bracket is put in front of the division sign:-
=DateDiff("n",[fldtimestarted],[fldtimeended]) / 60

Your expression is much simpler and better than mine.

 

[LisaJill]

What if I just wanted this calculated in a report? So I'm entering a Time Sheet and I input the dates to generate it on...

If I put in an unbound text box and just edit the Control Source in Data

Sum(DateDiff("n",[fldtimestarted],[fldtimeended]) / 60 )

but it gives me bracket errors and once it gave me comma errors....

Do I have to run this from a query? Is that the proper way to do it or can I jump it right into the report somehow?

Thank you again for your help =)

 

[EMP]

You can create a query using the expression DateDiff("n",[fldtimestarted],[fldtimeended])/60 AS Hours, e.g.

SELECT tblTimeSheet.DateField AS [Date],
tblTimeSheet.fldtimestarted AS [Start Time],
tblTimeSheet.fldtimeended AS [End Time],
DateDiff("n",[fldtimestarted],[fldtimeended])/60 AS Hours
FROM tblTimeSheet;

Then build a report based on this query. Put a text box on the Report Footer and set the text box's Control Source to:
=Sum([Hours])

 

[raskew]

Here are a couple of functions which you might adapt to your purposes:

Function ElapTime(stime As Date, etime As Date, factor As Integer) As Single
'*******************************************
'Name:      ElapTime (Function)
'Purpose:   Return the difference between two
'           date/time fields as a number/decimal
'           rounded to the nearest 1/n.
'Notes:     Works with both date/time and time
'           only input.  Does not work with times
'           (only) that span midnight.
'Inputs:    (1) ? elaptime(#06:00#, #11:42#, 4) 'nearest quarter-hour
'           (2) ? elaptime(#06:00#, #11:42#, 2) 'nearest half-hour
'           (3) ? elaptime(#12/15/02 18:00#, #12/17/02 02:41#, 4)
'Output:    (1)  5.75
'           (2)  5.5
'           (3) 32.75
'*******************************************

ElapTime = Int(DateDiff("n", [stime], [etime]) / 60 * factor + 0.5) / factor

End Function

'*******************************************

Function MinutesToString(ByVal numMins As Integer) As String
'*******************************************
'Name:      MinutesToString (Function)
'Purpose:   Convert minutes into an hr:min
'           string
'Inputs:    (1) ? MinutesToString(662)
'           (2) ? MinutesToString(datediff("n", #14:00#, #16:30#))
'Output:    (1) 11:02
'           (2)  2:30
'*******************************************
Dim strHrs As String
Dim strMins As String

strHrs = Int(numMins / 60)
strMins = Format(numMins Mod 60, "00")

MinutesToString = strHrs & ":" & strMins
End Function

 

[LisaJill]

EMP, thank you.

This is fantastic.

I have one other question. I Have a form Journal built off the table Journal.

I tried to put an unbound text box with =Sum([Hours]) in that form, but since its based off the table, not the query, it doesnt work.

Do I need to rebuild the form based off the query (and adding the other info I need) or can I somehow reference it the way it is?

I tried using the expression builder and it came out somehting like

=Sum[HourQuery]![Hours]

but that just got me #name....

thanks =)

Also, thank you for the functions.. I am not yet at the stage where I even know how to implement them/call them, but I've made a note of them and will look at them when I have some time to learn about that =)

 

[EMP]

For an unbound text box on a form, you can use the DSum() function. The function can be used on a table or a query.

When you open the form in the attached DB, you can see the total hours calculated using the DSum() function on both the table and a query.

The DB also contains a report based on the query.


The DB is in Access 97. If you use Access 2000 or 2002, you can choose Convert and save with a new name when the DB is opened.

 

[LisaJill]

You arer fantastic, thank you.

It's amazing how this personal project has grown. It was really a database to track my internship initially. But with all I've learned I've expanded it to track my job (also hourly) create and email invoices and timesheets anad am stilla dding more.

I couldn't do it without all the help I've received here, so thank you =)

 

[LisaJill]

Ok, this is fantastic, and I have it calculating total hours, thank you!

The only problem is that i can't get it to caluclate total hours by company. I tried a few t hings.

The way this is set up is I have a Company Form. I think have the Journal as a Subform. The Subform only shows me entries relating to the proper company, which is fantastic.

But the DSUM isn't restricting it that way. I tried adding Company (via the query and via Journal) to the query and it still won't restrict it.

I tried making it =DSUM ("Company", "Hours", "HoursQuery") as well with no luck.

It still just totals all of it.

Also, I had one mor equestion while you're here if its possible =) I need to then take the total hours for the company and divide by 45 (Its 45 housr per class credit... I need to know how many credits I've earned to date per company). Can I just add a /45 at the end?

I tried to attach the database but it was too big, you can download it here if it helps:

http://www.lisa-jill.com/Job Tracker.zip

It's case sensitive =)

The relevant forms are Company and Journal, the relevant query is HoursQuery. It's a bit messy as I've been learning with it, my apologies =/

Thank you so much for your help!

 

[EMP]

We can reference a control on a form with
[Forms]![formName]![controlName]

So you can use:-
=DSum("Hours","HoursQuery","Company='" & [Forms]![Company]![Company] & "'")


Since the value returned by a DSum() is numeric, it can be used in calculations. You can add /45 at its end.

 

[LisaJill]

Brilliant, exactly what I needed to know.

Thank you so so so so much!