I am sure this should be easy but I am struggling. Basically I have a start time text box(hours+mins)and a finish time text box(hours+mins)on a form. I want to return the elapsed time between the two (i.e.) the hours and inutes worked and then I want, in a third txt box, to calculate the value of the elapsed time against a cost per hour. So for example if the time elapsed was 3 hours 23 mins and the hourly rate was £45 with a minute rate of 75p (45/60)I want the first calculation to calculate the 3hours 23 mins and the second calculation to calculate the (3*45 + 23*.75). Is there a neat way of doing this?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Calculations in Text boxes
- Thread starter mondeoman
- Start date
MikeC14081972
Programmer
Access will always store a date and time in a 'Date/Time' field. therefore you can use the following
DateDiff ("n",[start time],[end time])
This will give you the number of minutes elapsed. It just a case of multiplying by your minute rate.
Hope that helps
If you make something idiot proof - They'll Only make a better idiot!!!
DateDiff ("n",[start time],[end time])
This will give you the number of minutes elapsed. It just a case of multiplying by your minute rate.
Hope that helps
If you make something idiot proof - They'll Only make a better idiot!!!
- Thread starter
- #3
Yes thank you for that. The only trouble is that I want to show in a report the hours and minutes worked. If I divide the minutes by 60 this shows the hours and fraction of hours. So for example 220 minutes is in reality 3 hours and 40 minutes but the above calculation makes it 3.66 hours recurring. I wondered whether there was either a calculation or function that did the conversion?
MikeC14081972
Programmer
OK.
Use the above to calulate the amount of minutes worked as a calculated field in your query (Alias name TimeWorked in this example)
Then place a text box on your report and in the control source add the following formula
=[TimeWorked] \ 60 & Format([TimeWorked] Mod 60, "\:00")
If for example you have a Start Time of 12:00 and an End Time of 15:20 your report will show 3:20.
Hope this helps
If you make something idiot proof - They'll Only make a better idiot!!!
Use the above to calulate the amount of minutes worked as a calculated field in your query (Alias name TimeWorked in this example)
Then place a text box on your report and in the control source add the following formula
=[TimeWorked] \ 60 & Format([TimeWorked] Mod 60, "\:00")
If for example you have a Start Time of 12:00 and an End Time of 15:20 your report will show 3:20.
Hope this helps
If you make something idiot proof - They'll Only make a better idiot!!!
MikeC14081972
Programmer
Meant to add
using \ instead of / to return whole hours only. Mod will return left over minutes after hours divided by 60.
I had a similar requirement a while back and found the above solution on Allen Brownes site. There is more info on his site if you want to take this further.
using \ instead of / to return whole hours only. Mod will return left over minutes after hours divided by 60.
I had a similar requirement a while back and found the above solution on Allen Brownes site. There is more info on his site if you want to take this further.
- Thread starter
- #6
- Status
Similar threads