Formatting Result of Date AND Time Difference

  • Thread starter Thread starter VWP1
  • Start date Start date
V

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).
 
VWP1 said:
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).
Click to expand...


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,

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

Marshall Barton said:
VWP1 said:
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).
Click to expand...


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")
Click to expand...
 
VWP1 said:
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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

=(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?
Click to expand...

Right.
 
Back
Top