Formatting Result of Date AND Time Difference

[VWP1]

I am subtracting a date and time (General Format) from Now() and wish to see
this in the form of Days:Hours:Minutes for purposes of Payroll, for my
business. "Clocking in" is maintained with date as well as time (General
Format) and I've an Access form with a query of those who have not clocked
out. I am using SQL to feed the form, and need a calulated field which
converts the decimal duration into "clock" hours (Hours and minutes since
clocking in).

 

[Marshall Barton]

I am subtracting a date and time (General Format) from Now() and wish to see
this in the form of Days:Hours:Minutes for purposes of Payroll, for my
business. "Clocking in" is maintained with date as well as time (General
Format) and I've an Access form with a query of those who have not clocked
out. I am using SQL to feed the form, and need a calulated field which
converts the decimal duration into "clock" hours (Hours and minutes since
clocking in).

If your durations are in minutes, i.e. DateDiff("n", [time
in], [time out]) then you can display the duration by using
a text box expression like:

(dur \ 1440) & ":" & (dur Mod 1440) \ 60 & ":" & (dur Mod
60)

or, if you want leading zeros with the hours and minutes:

(dur \ 1440) & Format((dur Mod 1440) \ 60, "\:00") &
Format(dur Mod 60, "\:00")

 

[VWP1]

Marshall,

Upon using DateDiff for Time,..I'd not thought of it; I thought it was only
usable for dates. It worked for time, but I now have an Integer in the
calculated field of the query.

Regarding the textbox on my Access form, the textbox is fed by the
calculated field - in the query - named [dur6_z].

But you said..."expression" and this tells me that I should place the equals
sign in there, right? So I put the following into the control source of the
textbox with th equals sign, and it worked, and even took teh extra spaces
out. I had to change the dur to dur6_z to match my calculated fieldname.

=(dur \ 1440) & ":" & (dur Mod 1440) \ 60 & ":" & (dur Mod>60)

1) What does Mod mean?
2) What is the 1440 for?
3) I think I need another mask, now that the textbox reads 0:7:55 and so I
am interested in reading: 1 day(s), 7 Hour(s), 55 Minute(s) since logging on.
4) I take it that the mask you gave me is : days:Hours:Minutes right?

VWP1

I am subtracting a date and time (General Format) from Now() and wish to see
this in the form of Days:Hours:Minutes for purposes of Payroll, for my
business. "Clocking in" is maintained with date as well as time (General
Format) and I've an Access form with a query of those who have not clocked
out. I am using SQL to feed the form, and need a calulated field which
converts the decimal duration into "clock" hours (Hours and minutes since
clocking in).

If your durations are in minutes, i.e. DateDiff("n", [time
in], [time out]) then you can display the duration by using
a text box expression like:

(dur \ 1440) & ":" & (dur Mod 1440) \ 60 & ":" & (dur Mod
60)

or, if you want leading zeros with the hours and minutes:

(dur \ 1440) & Format((dur Mod 1440) \ 60, "\:00") &
Format(dur Mod 60, "\:00")

 

[Marshall Barton]

But you said..."expression" and this tells me that I should place the equals
sign in there, right? So I put the following into the control source of the
textbox with th equals sign, and it worked, and even took teh extra spaces
out. I had to change the dur to dur6_z to match my calculated fieldname.

=(dur \ 1440) & ":" & (dur Mod 1440) \ 60 & ":" & (dur Mod>60)

1) What does Mod mean?

Mod it the remainder operator
\ is the integer divide operator

When you run into something in someone's posted code,
expression or SQL that you are not familiar with, the first
thing you should do is try to find it in VBA Help (not
Access Help).

2) What is the 1440 for?

24 hous in a day times 60 minutes in an hour.

3) I think I need another mask, now that the textbox reads 0:7:55 and so I
am interested in reading: 1 day(s), 7 Hour(s), 55 Minute(s) since logging on.

=(dur \ 1440) & " day(s), " & (dur Mod 1440) \ 60 & "
Hour(s), " & (dur Mod>60) & " Minute(s) since logging on"

4) I take it that the mask you gave me is : days:Hours:Minutes right?

Right.