Time elapsed to decimal (1 Viewer)

[LOUISBUHAGIAR54]

My problem has been met with others before but despite adequate searches I have not managed to solve it.

I am creating query that calculates time interval so that this is used to calculate pay. Employees clock in at [time_in] and clock out at [time_out]. Sometimes time_out is on the next day of time_in.

So if is substract time_out - time_in I will get a negative number.

I am therefore using the following formula to extract the time interval;
Interval: Format([time_out]-[time_in],"Short Time")

this gives out a result such as 3:30 when in fact I would need 3.5 to calculate pay by multiplying with rate.

How do I convert 3:30 to 3.5. From the searches I get that I need to multiple 3:30 by 24. But when in put done in the query
Interval: Format([time_out]-[time_in],"Short Time")*24

it will not work out. I get an error.


Can anyone please help with my problem ?

Louis Buhagiar

 

[pbaldy]

One way:

http://access.mvps.org/access/datetime/date0004.htm

 

[LOUISBUHAGIAR54]

Dear Paul,

In fact i got the formula
Format(time_out - time_in,"Short Time") from one of your previous posts. It is working fine in the sense that it is giving me results such as 3:30, 12:00 etc.

However I need to have a number such as 3.5 and 12 instead, so that I can use this to calculate pay by multiplying by another field which has the pay/hr in euro.

I need to convert the 3:30, 12 etc to 3.5, 12. Do you have a way to do this in the query design ?

Many thanks.

Louis

 

[vbaInet]

If your minutes are always in halves or zeros you can calculate the Minute() side by dividing it by 6. Finally combine both the Hour() and the newly calculated decimal.

 

[RainLover]

I need to convert the 3:30, 12 etc to 3.5, 12.

What is what here. Just for clarification what is 12. Days, Hours, Minutes or Seconds.

3:30 should be half past 3:00. Or should I say a proper Date/Time Format.
So what is 3.5 and 12.
How does 12 relate to 3.5.

 

[LOUISBUHAGIAR54]

3:30 refers to three hours thirty minutes . 3.5 refers to 3.5 hours
12:00 refers to twelve hours while 12 refers to 12 hours.

3:30 will not be good to calculate pay, whilst 3.5 will be. The same goes for 12:00 and 12.

I hope I have made myself clear. Your help is appreciated.

Thanks


Louis Buhagiar

 

[vbaInet]

Did you miss my post above? Or are you not keen to have a go at it?

 

[pbaldy]

Did you edit the original post? In any case, what I do is use DatePart() to get the hours and minutes. I divide the minutes by 60 and add to the hours (so 30/60 = .5, added to 3 = 3.5)

 

[RainLover]

12:00 refers to twelve hours while 12 refers to 12 hours.

This is the same Horse just with a different Jockey.

I think you need to be more precise in this regard. No computer language is able to guess what you really mean.