Doing Math on time

Topic

New Reply

I have a report with the following fields

[signed on]
[personel]

both are formatted h:nn:ss

what I am trying to do is calc the percent of time which is spent in [personel] as it relates to the overall time [signed on]

I have tried making a field = [personel] / [signed on] formatted percent

but this does not work all I get is 0.00%

ex: [signed on] 0:10:00 and [personel] 0:02:30 should give you a percent of 25%

I am guessing it is because I am trying to do the math on times and not general numbers.

Any help would be appreciated.

Reply | Quote

Replies

I have tried making a field = [signed on] / [personel] formatted percent

Times are just stored as numbers, so you can do arithmetic with them. (things get more complicated if you have times exceeding 24 hrs)

From you description, you have this the wrong way around.

Try: = [personel] / [signed on] formatted percent

Reply | Quote

Times are just stored as numbers, so you can do arithmetic with them. (things get more complicated if you have times exceeding 24 hrs)

From you description, you have this the wrong way around.

Thanks I noticed that just as I read your message….

as for the result….. I have tried that formula (the right one ) and the result is not right here is what I have….

[Signed on] 16:47:11
[personal] 00:11:24

When I do this is excel to check my work 1.13% the correct answer as per other reports I am working from
when I do this is access

=[Personal]/[Signed On] formatted as percent answer I get back as 2.29%

Reply | Quote

Times are just stored as numbers, so you can do arithmetic with them. (things get more complicated if you have times exceeding 24 hrs)

Things just got more complicated as I have times exceeding the 24 Hr mark…. They are just showing a time because the date changed…. How can I get it to show

100:25:00
h:mm:ss

Reply | Quote

When I do the calculation in Access i get the same answer as I (and you) get in Excel. Here it is done in a query.

Can you post a demo version of the db.

Reply | Quote

John,

I think I solved the issue. I attacked the formula from the query insted of on the report and it worked great. I dont know why the report version did not function but it is working now.

Reply | Quote

I dont know why the report version did not function but it is working now.

I don’t know why it makes any difference either, but the important thing is that you now have something that works.

Reply | Quote

John,

Sometimes I have learned not to ask questions when something start to work… lol

Reply | Quote

Access will never display a time in the way you want (unlike Excel, which will if you set the correct format…I forget just what it is).
You can perform a whole lot of calculations to break the time into three separate amounts: hours, minutes and seconds as three separate numbers, then display the three separate values. But it is a fair bit of work.

Dates and Times are stored in the same datatype, with the whole number part representing the days, and the decimal bit the time component. So once the time biit goes over 24 it just becomes a day.
So you could display it as days, hours, minutes and seconds.

But the calculation to calculate the percentage will still be correct.

When I need to work with times exceeding 24 hours, I just use a double variable type to represent the number of hours. So 27.5hrs means 27 hrs 30 minutes.
If I am calculating this from a Start and Finish Time, you need to multiply the difference by 24 to make the conversion.

To take this further, we would need to know more about where the times come from..how they get entered or calculated, and what you want to do with them.

Reply | Quote

Hi,
the excel format you’d be looking for is [h]:mm:ss (the square brackets are the key)

If you need to show negative times, you need to change to the 1904 date format in Excel.

In Access, try this as a control source in your report:

=int(24*[signed on])& “:” &int(((24*[signed on])-int(24*[signed on]))*60)& “:” &int((24*60*[signed on]-int(24*60*[signed on]))*60)

Jim

Reply | Quote