Time

[alastair69]

Dear All,

I am not sure if this has been raised before, put after a long list of entries, i have given up.

I have a start time of 09:00 and a finish time of 09:30 the total time is 0.30 easy so far, but when I times the total by an hourly rate the cost total is incorrect as it does not see 0.30 as 1/2 an hour (0.5), has any body got any ideas of how to over come this problem.

Thanks for all your help in advance


Alastair Lane

 

[Mile-O]

Dates are kept as 8 byte floating point numbers. The first four bytes are the left side of the decimal and contain the date. The right side of the decimal point is the time of the day thus 0.5 is midday.

So, since 0.5 is representative of 12 hours out of 24 and there's 24 half hours in 12 hours then divide 0.5 by 24 and multiply by that to get half hour rates.

 

[alastair69]

Dates are kept as 8 byte floating point numbers. The first four bytes are the left side of the decimal and contain the date. The right side of the decimal point is the time of the day thus 0.5 is midday.

So, since 0.5 is representative of 12 hours out of 24 and there's 24 half hours in 12 hours then divide 0.5 by 24 and multiply by that to get half hour rates.

Thanks for your help, have you got a simple exmple as i am not sure how to treat the 0.30 as an total time on a form to calulate the cost. I have tried to take [TotalTime] 0.30/24*24.

[starttime] 09:00 [FinishTime] 09:30 [TotalTime] 00:30

I thank you again.

Alastair

 

[ScottGem]

What I think you are missing here is that what is stored as a result of the calculation is not 30 minutes but a decimal value of .0208333. This is equivalent to 1/48 of a day. When doing time calculations its usually better to calculate the minutes and then go from there.

DateDiff("m",[Starttime],[EndTime])

will return 30 as the number of minutes.

The next step is to do:

DateDiff("m",[Starttime],[EndTime]) /60

To get the hours as a numerical value (i.e. .5 in this case)

Finally

(DateDiff("m",[Starttime],[EndTime]) / 60) * [Rate]

Will get you the value I think you are looking for.

One Note; if you time difference spans more than 24 hours or past midnight, then you need to include the date in your time field.

 

[Bat17]

[totalTime]*24*[costPerHour] = [totalCost]

HTH

Peter

 

[alastair69]

Thank you all for helpping me

 

[alastair69]

What I think you are missing here is that what is stored as a result of the calculation is not 30 minutes but a decimal value of .0208333. This is equivalent to 1/48 of a day. When doing time calculations its usually better to calculate the minutes and then go from there.

DateDiff("m",[Starttime],[EndTime])

will return 30 as the number of minutes.

The next step is to do:

DateDiff("m",[Starttime],[EndTime]) /60

To get the hours as a numerical value (i.e. .5 in this case)

Finally

(DateDiff("m",[Starttime],[EndTime]) / 60) * [Rate]

Will get you the value I think you are looking for.

One Note; if you time difference spans more than 24 hours or past midnight, then you need to include the date in your time field.

I have tryed your formula but i get the answer as 0.00 i am carrying out this in a query, have you any other idea's i can try.

 

[ScottGem]

Can you post your SQL.

 

[EMP]

If StartTime and FinishTime are two date/time fields, you can get the number of hours (with several places of decimal) with the expression:-
(FinishTime - StartTime) * 24.


I have attached a sample database. You can open the form, enter a StartTime and a FinishTime, and click on the command button to calculate the numeric TotalHours. The code is in the On Click event of the command button:

Private Sub cmdCalc_Click()
   Me.TotalHours = [b](Me.FinishTime - Me.StartTime) * 24[/b]
End Sub

The StartTime and FinshTime text boxes have been formatted as Short Time.


The formula TotalHours = (FinishTime - StartTime) * 24 is taken from Jon K's post in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=78716

In the database, I have also included a query for calculating Cost (TotalHours * HourlyRate). The query is adapted from the query in Jon's database.

^

 

[ScottGem]

If StartTime and FinishTime are two date/time fields, you can get the number of hours (with several places of decimal) with the expression:-
(FinishTime - StartTime) * 24.

That will only work if the date is included with the time or if the times are within the same day.

 

[EMP]

That will only work if the date is included with the time or if the times are within the same day.

Can you give an an example when it will not work?

^

 

[EMP]

What I think you are missing here is that what is stored as a result of the calculation is not 30 minutes but a decimal value of .0208333. This is equivalent to 1/48 of a day. When doing time calculations its usually better to calculate the minutes and then go from there.

DateDiff("m",[Starttime],[EndTime])

will return 30 as the number of minutes.

The next step is to do:

DateDiff("m",[Starttime],[EndTime]) /60

To get the hours as a numerical value (i.e. .5 in this case)

Finally

(DateDiff("m",[Starttime],[EndTime]) / 60) * [Rate]

Will get you the value I think you are looking for.

One Note; if you time difference spans more than 24 hours or past midnight, then you need to include the date in your time field.

Do you think (DateDiff("m",[Starttime],[EndTime]) / 60) * [Rate] really work? Have you tested it?

^

 

[stephen81]

Do you think (DateDiff("m",[Starttime],[EndTime]) / 60) * [Rate] really work? Have you tested it?

^

You need "n" instead of "m".
You can check the different intervals available in help, but "m" is months, "n" is minutes.

 

[alastair69]

Sorted Promblem

After a few hours looking lastnight i have sorted my promblem by using the following:
In a query
TrueCost: TimeValue([billablehours])*[BillingRate]*24

A very big thank you to you all.

Alastair Lane

 

[ScottGem]

Yes, I goofed. The interval should have been n not m for minutes. If I make a mistake (and I'm not perfect) I appreciate it when someone points it out. Its more important that the correct info is given. There was no call for the tone that EMP used to point out the error. Thank you Stephen for pointing out the correct interval.

But subtracting end time from start time will not work correctly if the time spans midnight unless the date is also included. Subtracting 6am from 3pm will not give 15 hours instead it give 9 hours.

 

[stephen81]

No probs Scott. Easy mistake to make, I had to check the help file for the correct interval code myself. "n" doesn't seem the logical choice. I would have thought "mm" for month and "m" for minute might be more logical, but I guess the guys at MS have probably got their reasons.

I didn't read EMPs post as having a dig at you though, I just thought he/she had maybe tried it and not been able to get it to work, so wanted to check that you had posted it correctly. If I'm wrong and he was having a dig, then there is no need for that sort of post. Hopefully he wasn't having a dig though. Maybe we should give him the benefit of the doubt.

 

[EMP]

If I make a mistake (and I'm not perfect) I appreciate it when someone points it out.

Really?

.

But subtracting end time from start time will not work correctly if the time spans midnight unless the date is also included. Subtracting 6am from 3pm will not give 15 hours instead it give 9 hours.

Have you read the note in Jon K's post:

Note: If the EndTime crosses mid-night, the dates must also be entered in the two time fields (as shown in the last three records in the table.)

If the dates are stored in two separate fields in the table, you can use the following expression to return the time in Hours:-
(([EndDate]+[EndTime]) - ([StartDate]+[StartTime]))*24

^

 

[ScottGem]

Yes really! You (Emp) obviously have not seen very many of my posts if you you don't believe that. I have been doing this (helping out on Q&A message boards) for a very long time. It has always been more important to me that the correct info be given the asker. You really should do better research before questioning a statement like that.

As for my reading Jon K's post, no I didn't read the linked thread. That the information was included in that thread doesn't make it wrong for me to reiterate it.

Based on the 2 threads here where we seemed to have butted heads, it would appear that you are the one with the problem. In both cases, I added alternative ways of accomplishing what the asker wanted. In both cases my advice was correct except for the typo of m/n. In neither case did I attack the other solutions offered. I simply offered my own preference for a solution. yet in both cases you seem to have taken offense for some unknown reason.

 

[DLB]

I would use "s" instead of "n". It won't leave out the seconds.

DateDiff("s",[StartTime],[EndTime])/3600*[Rate]

 

[stephen81]

Really?

Scott, it looks as though you may have been right about EMP's tone.
EMP, these forums are all about helping each other out. There's no place here for having a go at people for helping. Scott's solution was essentially correct apart from the m/n typo. If you see solutions posted that may be incorrect, why not just politely post a correction, which could benefit members looking for a solution, instead of having a dig at people for trying to help.