Calculating Time
 

I am looking for a formula that would calculate how many minutes someone
was late.

For example: If an employee's schedule was 9:00am-5:00pm and they
arrived after 9:07am but before 9:22am they would be charged .25hrs, if
they arrived after 9:23am but before 9:37am they would be charged .50hrs
and so on.

Since we have many different work schedules it would be great to figure
out how to create a formula for these calculations... a formula to
calculate the time charged. I have Excel 2000. Does anyone know how
to do this? Please help!

Thanks


--
Padovan
------------------------------------------------------------------------
Padovan's Profile: http://www.excelforum.com/member.php...o&userid=26432
View this thread: http://www.excelforum.com/showthread...hreadid=397015

Calculating Time
 

Maybe

=IF(K1=TIME(9,7,0),INT((K1-TIME(9,7,0))*24*4)+1,0)*25

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Padovan" wrote in
message ...

I am looking for a formula that would calculate how many minutes someone
was late.

For example: If an employee's schedule was 9:00am-5:00pm and they
arrived after 9:07am but before 9:22am they would be charged .25hrs, if
they arrived after 9:23am but before 9:37am they would be charged .50hrs
and so on.

Since we have many different work schedules it would be great to figure
out how to create a formula for these calculations... a formula to
calculate the time charged. I have Excel 2000. Does anyone know how
to do this? Please help!

Thanks


--
Padovan
------------------------------------------------------------------------
Padovan's Profile:
http://www.excelforum.com/member.php...o&userid=26432
View this thread: http://www.excelforum.com/showthread...hreadid=397015

Calculating Time
 

Hi Bob,

Thanks for the info but in order for me to be able to set up the chart,
could you please explain it?

=IF(K1=TIME(9,7,0),INT((K1-TIME(9,7,0))*24*4)+1,0)*25

I understand part of it but if you can explain it I would greatly
appreciate it!

Thanks


--
Padovan
------------------------------------------------------------------------
Padovan's Profile: http://www.excelforum.com/member.php...o&userid=26432
View this thread: http://www.excelforum.com/showthread...hreadid=397015

Calculating Time
 

Let me see now, it has been a while since I wrote it.

It says

If the time is greater than or equal to 9:07 (K1=TIME(9,7,0))
Subtract 9:07 from that time (K1-TIME(9,7,0))
Multiply that result by 24*4 to get the number of quarter hours in there
(INT((K1-TIME(9,7,0))*24*4))
Add 1 to get the correct number of late periods since the time in K1
Multiply by 25 minutes per late period

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Padovan" wrote in
message ...

Hi Bob,

Thanks for the info but in order for me to be able to set up the chart,
could you please explain it?

=IF(K1=TIME(9,7,0),INT((K1-TIME(9,7,0))*24*4)+1,0)*25

I understand part of it but if you can explain it I would greatly
appreciate it!

Thanks


--
Padovan
------------------------------------------------------------------------
Padovan's Profile:
http://www.excelforum.com/member.php...o&userid=26432
View this thread: http://www.excelforum.com/showthread...hreadid=397015

Calculating Time
 

Hi Bob,

Thanks for the definition. Unfortunately it is producting FALSE
results. I don't know what I'm doing wrong. I decided to send a copy
of the manual chart for the 9:00am-5:00pm schedule... might be helpful.
I appreciate your time on this.

Min
Charged 9:00-5:00 schedule
..25 hrs = 9:08 am –9:22 am
..50 hrs = 9:23 am – 9:37am
..75 hrs = 9:38 am – 9:52 am
1.00 hr = 9:53 am – 10:07am
1.25 hrs = 10:08am – 10:22 am

and so on.

Please let me have your thoughts and thanks


--
Padovan
------------------------------------------------------------------------
Padovan's Profile: http://www.excelforum.com/member.php...o&userid=26432
View this thread: http://www.excelforum.com/showthread...hreadid=397015

Calculating Time
 

Mistakenly read the .25 hours as 25 m ins :-(.

Do you want decimal hours

=IF(K2TIME(9,7,0),INT((K2-TIME(9,8,0))*24*4)+1,0)*0.25

formatted as general, or time

=IF(K1TIME(9,7,0),INT((K1-TIME(9,7,0))*24*4)+1,0)/4/24

formatted as time

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Padovan" wrote in
message ...

Hi Bob,

Thanks for the definition. Unfortunately it is producting FALSE
results. I don't know what I'm doing wrong. I decided to send a copy
of the manual chart for the 9:00am-5:00pm schedule... might be helpful.
I appreciate your time on this.

Min
Charged 9:00-5:00 schedule
25 hrs = 9:08 am -9:22 am
50 hrs = 9:23 am - 9:37am
75 hrs = 9:38 am - 9:52 am
1.00 hr = 9:53 am - 10:07am
1.25 hrs = 10:08am - 10:22 am

and so on.

Please let me have your thoughts and thanks


--
Padovan
------------------------------------------------------------------------
Padovan's Profile:
http://www.excelforum.com/member.php...o&userid=26432
View this thread: http://www.excelforum.com/showthread...hreadid=397015