Timing with Funncitons

Dear All Experts

i am New to Excel

so i don't know Formula's

I Wanted to Make a Sheet for Attendance with Time & Point Based to count Over Time.

Basically We are having small office with 3 Persons. so Wanted automatic Time Counter

My Office Time is 9:30 A.M to Evening 7:30

My Qurey is how to Calculate over times.


What we are having now, Every Half an hour is 00.50 Score for Over Time

Like Somebody Come Early in Morning instead of

Before Office time 9:30 A.M
9:00 A.M he Gets 00.50
8:30 A.M he Gets 01.00
8:00 A.M he Gets 01.50
7:30 A.M he Gets 02.00

Same thing in Evening

After 7:30 P.M Overtime Stats
8:00 P.M he gets 00.50
8:30 P.M he gets 01.00
9:00 P.M he gets 01.50
9:30 P.M he gets 02.00
10:00 P.M he gets 02.50
10:30 P.M he gets 03.00
1100 P.M he gets 03.50
11:30 P.M he gets 04.00

Can u Tell anyone how to do this automatic counting in Next Cell.

Reference Sheet Attached

Waiting for your Reply.

I'm confused. The "Out" times in column F are earlier than the "In" times in column D......same date. Is this the data you want us to work with or should I look elsewhere on the sheet?

And in what column do you want the results?

yes sir

Sorry for the Same it will come in P.m

as i dont know much in excel it happen with me

Once Again Sorry.

In time is Morning Time

and Out Time is Evening Time

Please find the changes made in your workbook attached.

In cell I399 I entered the Office begin time of 9:30 AM. In cell I400 I entered the Office end time of 7:30 PM. I then named each of these cells in Name Manager naming them InTime and OutTime. These are the references I used in this formula entered into H399 - H415 except for holidays and where there are no out times entered.

Formula:

Please Login or Register  to view this content.

Does this do what you want?