Convert Time

[Encoder in Distress]

Hi.

I have a simple bundy clock application and I want to set the time to 08:00
Am when and an employee clocks in say, 07:30 Am. and set the Punch Out to
05:00 PM when he/she punches at 05:30 Pm.

Any ideas? Please help. Thank you very much!

 

[Henry]

I guess you want to round up or down to the full hour. Did you try as simple
as:

myTime = Hour(myTime)+1

or

myTime = Hour(myTime)-1

henry

 

[Marshall Barton]

I have a simple bundy clock application and I want to set the time to 08:00
Am when and an employee clocks in say, 07:30 Am. and set the Punch Out to
05:00 PM when he/she punches at 05:30 Pm.

That's too vague a description of what you want to do.

What if the clock in time is 7:45 or 6:15? Similar for
clock out times.

Is the time values you are working with text strings or
Date/Time values. Do they include a date part?

WHere are you tring to do this? In a data entry text box on
a form? In a query? In a report text box? In a VBA
procedure?

If you want all clock in times before 8:00 to be set to
8:00, all clock out times after 5pm to be set to 5pm and the
values are in a Date/Time variable that includes a date
part, then a VBA code snipit like this at least demonstrates
the kind of logic you could use:
If TimePart(clockin) < #05:00# Then
clockin = DatePart(clockin) + #05:00#
End If

 

[Encoder in Distress]

Hi, Marsh. Thanks for the prompt reply.

The purpose of converting the clock in/clock out time is to avoid over
calculation of the total hours worked. Because when an employee clocks in say
7:00AM, her/his total hours for the day would be 9 hours instead of only 8
hours.

I intend to use the procedure in my Timesheet Query and clock in/clock out
time is a Date/Time variable with no date part.

My tblTime has the following fields:

Time_ID (Autonumber)
Date (Date/Time) default value is Now()
Am_In (Date/Time) default value Time()
Pm_Out (Date/Time) default value Time()

Hope you could help me further on this and I really appreciate your efforts.

Thank you very much.

 

[Marshall Barton]

OK, you can calculate a MinutesWorked field in the query:

MinutesWorked: DateDiff("n", IIf(Am_In < #8:00#, #8:00#,
Am_In), IIf(Pm_Out > #17:00#, #17:00#, Pm_Out))

The reason for using minutes instead of hours is so you can
deal with fractional hours. For example, if you want a
Pm_Out time of 4:45pm to be 7.75 hours, then divide the
minutes by 60. If you want the hours worked to be formatted
like 7:45, then use another expression (in a for or report
textbox) for displaying the formatted result:

=MinutesWorked \ 60 & Format(MinutesWorked Mod 60, "\:00")

 

[Encoder in Distress]

Hi, Marsh! Good evening!

Thanks for the help though what you taught me is not what I intend to do
although I was able to calculate the total minutes worked thru your code and
I appreciate it.

I am so sorry if my recreating of the problem was too vague to understand.
Now What I really want to perform is to automatically set the Time_In of an
employee to 8:00 am when say he punched in at 7:45am. Is it possible? I
found your reply few days ago helpful but my Time_In field (Date/Time) has no
date part.Thanks again for the help and God bless you!

OK, you can calculate a MinutesWorked field in the query:

MinutesWorked: DateDiff("n", IIf(Am_In < #8:00#, #8:00#,
Am_In), IIf(Pm_Out > #17:00#, #17:00#, Pm_Out))

The reason for using minutes instead of hours is so you can
deal with fractional hours. For example, if you want a
Pm_Out time of 4:45pm to be 7.75 hours, then divide the
minutes by 60. If you want the hours worked to be formatted
like 7:45, then use another expression (in a for or report
textbox) for displaying the formatted result:

=MinutesWorked \ 60 & Format(MinutesWorked Mod 60, "\:00")
--
Marsh
MVP [MS Access]

The purpose of converting the clock in/clock out time is to avoid over
calculation of the total hours worked. Because when an employee clocks in say
7:00AM, her/his total hours for the day would be 9 hours instead of only 8
hours.

I intend to use the procedure in my Timesheet Query and clock in/clock out
time is a Date/Time variable with no date part.

My tblTime has the following fields:

Time_ID (Autonumber)
Date (Date/Time) default value is Now()
Am_In (Date/Time) default value Time()
Pm_Out (Date/Time) default value Time()

Hope you could help me further on this and I really appreciate your efforts.

Thank you very much.

 

[Marshall Barton]

Then just use calculated fields in your query.

AdjTimeIn: IIf(Am_In < #8:00#, #8:00#, Am_In)
AdjTimeOut: IIf(Pm_Out > #17:00#, #17:00#, Pm_Out)

Then your form/reports can use the AdjTimeIn and AdjTimeOut
fields for display and further calculations.

 

[Encoder in Distress]

Hi, Marsh. Thanks for the help. It worked but when I presented my application
to the higher ups, they asked me one more question. What if the employees
rendered an overtime until 7 pm and others until 9 pm and so on.

I had this column named AdjTotalHrs in my query and your code worked well.
Now do I have to add another 5 columns for the following (projected) overtime?
7PM
8PM
9PM
10PM
11PM
12MN

I tried to modify your code to:

AdjTimeOut: IIf(Pm_Out > #17:00# and Pm_Out < #18:00#, #17:00#, Pm_Out),
IIf(Pm_Out > #18:00# and Pm_Out < #19:00#, #18:00#, Pm_Out),
IIf(Pm_Out > #19:00# and Pm_Out < #20:00#, #19:00#, Pm_Out),
IIf(Pm_Out > #20:00# and Pm_Out < #21:00#, #20:00#, Pm_Out),
IIf(Pm_Out > #21:00# and Pm_Out < #22:00#, #21:00#, Pm_Out),
IIf(Pm_Out > #22:00# and Pm_Out < #23:00#, #22:00#, Pm_Out),
IIf(Pm_Out > #23:00# and Pm_Out < #23:59#, #23:00#, Pm_Out))

but it didn't worked. The error says I have to enclose my text data in
quotes.
Please advise me what to do and thanks again for the help.

Then just use calculated fields in your query.

AdjTimeIn: IIf(Am_In < #8:00#, #8:00#, Am_In)
AdjTimeOut: IIf(Pm_Out > #17:00#, #17:00#, Pm_Out)

Then your form/reports can use the AdjTimeIn and AdjTimeOut
fields for display and further calculations.
--
Marsh
MVP [MS Access]

Thanks for the help though what you taught me is not what I intend to do
although I was able to calculate the total minutes worked thru your code and
I appreciate it.

I am so sorry if my recreating of the problem was too vague to understand.
Now What I really want to perform is to automatically set the Time_In of an
employee to 8:00 am when say he punched in at 7:45am. Is it possible? I
found your reply few days ago helpful but my Time_In field (Date/Time) has no
date part.Thanks again for the help and God bless you!

 

[Marshall Barton]

Hi, Marsh. Thanks for the help. It worked but when I presented my application
to the higher ups, they asked me one more question. What if the employees
rendered an overtime until 7 pm and others until 9 pm and so on.

I had this column named AdjTotalHrs in my query and your code worked well.
Now do I have to add another 5 columns for the following (projected) overtime?
7PM
8PM
9PM
10PM
11PM
12MN

I tried to modify your code to:

AdjTimeOut: IIf(Pm_Out > #17:00# and Pm_Out < #18:00#, #17:00#, Pm_Out),
IIf(Pm_Out > #18:00# and Pm_Out < #19:00#, #18:00#, Pm_Out),
IIf(Pm_Out > #19:00# and Pm_Out < #20:00#, #19:00#, Pm_Out),
IIf(Pm_Out > #20:00# and Pm_Out < #21:00#, #20:00#, Pm_Out),
IIf(Pm_Out > #21:00# and Pm_Out < #22:00#, #21:00#, Pm_Out),
IIf(Pm_Out > #22:00# and Pm_Out < #23:00#, #22:00#, Pm_Out),
IIf(Pm_Out > #23:00# and Pm_Out < #23:59#, #23:00#, Pm_Out))

but it didn't worked. The error says I have to enclose my text data in
quotes.

What are you trying to accomplish here?

What are your rules for Overtime? Five separate columns
sounds like there's something fishy in your idea.

Are partial hours of overtime discarded? or rounded?

Why does each hour mean something different?

 

[Encoder in Distress]

Hi, Marsh. Thanks for the prompt reply.

I don't really intend to have 5 overtime columns because that could give me
problems when generating the payslips.What I am trying to do is to round off
the time in AdjTimeOut Column. Here's a scenario:

When an employee clocks out at:

5pm but < 6pm ---> round off the time to 5pm.
6pm but < 7pm ---> round off the time to 6pm.
7pm but < 8pm ---> round off the time to 7pm
8pm but < 9pm ---> round off the time to 8pm
9pm but < 10pm --> round off the time to 9pm
10pm but < 11pm --> round off the time to 10pm
11pm but < 12Mn --> round off the time to 11pm

I was able to round off the 5:35PM using your code:

AdjTimeOut: IIf(Pm_Out > #17:00#, #17:00#, Pm_Out)

but the question is "what if an employee clocks out at 9:15Pm and he really
worked until 9PM?"

Please enlighten me on this and thank you so much for the efforts and time
you spent helping us. God bless!

 

[Marshall Barton]

If you just want to discard any partial hour then you can
use an expression like:
CDate(Int(Pm_Out * 24) / 24)
Or, if you need to fit it into your other question:

AdjTimeOut: IIf(Pm_Out > #17:00#, CDate(Int(Pm_Out * 24) /
24), Pm_Out)