left() of decimal?

[ewong1]

I currently have a calculation that takes a user driven start and end time[dtmStart] and [dtmEnd] and creates a calculated time frame ((dtmEnd-dtmStart)*60*24 AS [Minutes]) that returns the total number of minutes in between the two times. My next step converts the number into hours: ([Minutes]/60 AS [Hours]). Is there a way for me to extract the information from [Hours] left of the decimal(.) to give me the full hours?

Example:
Start Time: 10:02
End Time: 16:20
Minutes: 378
Hours: 6.3
----------Below are my desired results-----------
Final Hours: 6
Final Minutes: 18

Any ideas would be greatly appreciated. Thanks for your help!

 

[KenHigg]

Are you doing this with date/time flds?

 

[ewong1]

left() of decimal(.)

yes the fields are set up as dtmfields

 

[KenHigg]

Have you examined the built-in date/time functions in Access? i.e. DatePart(), DateDiff(), etc. They should get what you need.

 

[ewong1]

DateDiff() Function

Well since that worked so beatifully, I thought I would ask for more.. hehe

I was wondering if it is possible to get the total difference in times [dtmEnd]-[dtmStart] to return the value in the format of HH:MM where I am able to sum up a total of the values to get a grand total of hours and minutes spent over several records?

Example:
Start Time: 10:02
End Time: 16:20
Time in between: 6:18

Start Time: 08:48
End Time: 15:10
Time in between: 6:22

Total Time Spent: 12:40

Thanks!

 

[KenHigg]

Hum... format()

???

 

[Jon K]

I was wondering if it is possible to get the total difference in times [dtmEnd]-[dtmStart] to return the value in the format of HH:MM where I am able to sum up a total of the values to get a grand total of hours and minutes spent over several records?

You can use a Totals Query:

SELECT Int(Sum([dtmEnd]-[dtmStart]))*24+DatePart("h",Sum([dtmEnd]-[dtmStart])) & Format(Sum([dtmEnd]-[dtmStart]),":nn") AS [Total HH:MM]
FROM [TableName];
.

 

[Pat Hartman]

String functions do not properly work on numeric fields so Left(), Right(), etc. will not always return what you expect.

In help, look up arithmetic operators to see the difference between the two division operators / and \. You will find out how to return only whole numbers from a division.

 

[Veron]

Jon K

I wonder if you can help me with my problem. By adapting your expression in a Totals Query, I was able to correctly get the workers' weekly [Total HHMM]

Int(Sum([TimeEnd]-[TimeStart]))*24+DatePart("h",Sum([TimeEnd]-[TimeStart])) & Format(Sum([TimeEnd]-[TimeStart]),":nn") AS [Total HHMM]


But when I tried to multiply it by the workers' hourly rates to get the weekly wages
[Total HHMM]*[Hourly Rate] AS [Weekly Wages]

it all returned an #Error.

I have attached a simplified database. Can you help me to fix the error in the query?

Your help is greatly appreciated.

 

[Pat Hartman]

The value you calculated ends up as a text string. You can't do arithmetic with it.

 

[Jon K]

Since Sum([TimeEnd]-[TimeStart]) may exceed 24 hours, [Total HHMM] can exist only as a Text string, not a numeric DateTime value.

To get the numeric total hours, you can simply use:-
Round(Sum([TimeEnd]-[TimeStart])*24,4) AS [Total Hours]

It will return the [Total Hours] with 4 places of decimal. You can multiply it by the workers' [Hourly Rate] to get the [Weekly Wages].

See the query in the database.
.

 

[Veron]

Thanks a bunch.

With your expression of Sum([TimeEnd]-[TimeStart])*24, what used to be half an hour's work in Excel is now reduced to less than one second with an Access query. It's amazingly simple. No complicated divisions and multiplications.

I appreciate your help. Thanks again.

 

[ewong1]

Am I correct in assuming then that time cannot be a calculated number if the number is greater than 24 hours? Thanks for the clarification!

 

[Jon K]

DateTime in Access is internally a double precision number with the integer portion representing the day and the decimal portion representing the time.

Access's built-in display of the time portion of a numeric DateTime value is just like a clock. It goes from 23:59:59 to 00:00:00. At the same time, it internally increases the day portion (the integer) by 1.

When we deduct a StartTime from an EndTime, the result is still a numeric DateTime value and is therefore subject to the same 23:59:59 limit. Internally being a number, it can be used in numeric calculations.


However, when we manipulate the number by multiplying the integer portion by 24 and adding it back to the hours so that we can display the DateTime value as something like 36:25, we can only use a Text string. It is the same even when the integer is 0 so that we have only 12:25. Being a text string, naturally it cannot be used in numeric calculations.