Total Login Hours and Total Break Time in Excel

[KishorKK]

Hi All,

I would like to know how to calculate time between in AM and PM. When caluculating AM is taking fine but when PM is coming which formula is good. I am attaching sheet. Please help me with this. Thanks. It would be helpfull if i can able to know total hours of work time.


Regards,
Kishor

 

[Chihiro]

Few things:

1. You have AM/PM wrong for "Break Time - II - Out" (Row 3 & 4).
   • It should be 12:50 PM (12:50 in 24 hour clock) not 12:50 AM (00:50).
2. You need space between 2:00 & PM in cell G4.
   • Otherwise it will be treated as text and will result in #Value! error.
3. Total break time should be sum of two breaks

A question. Shouldn't Total login hours be...
=("Log Out - Time" - "Login Time") - "Total Break Time"
?

See attached.

 

[KishorKK]

Thanks a lot..

 

[KishorKK]

I have seen a video he used "MOD" function.. that's why i confused.

 

[KishorKK]

and here i have a question that can we make pivot table using login sheet to know which employee working more and less using pivot and slicer.

 

[Chihiro]

MOD is only needed if you have date stored along with time.

Excel stores date and time in underlying value. For an example "9/30/2016 10:00 AM" is stored as "42643.4166666667".

1 day = 1 and 1 hour = 1/24

So time portion is always less than 1.

To extract time from date & time... assuming above example is in A1.
=MOD(A1,1)

In plain English, it returns remainder when A1 value is divided by 1.
I.E. Portion after decimal place "0.4166666667"

Not sure what you mean by Slicer. For simple summary you just need pivot with calculated field. "Total Login Hours" - "Total Break Time".

See attached.

 

[KishorKK]

slicer is introduced in 2013 excel i think

 

[Chihiro]

I know. But for what purpose are you using slicer?

 

[KishorKK]

Hey you've inserted a new column named Cal1. How ? Means how to do? can you tell me

 

[Chihiro]

1. Insert Pivot
2. Go to PivotTable Tools ribbon tab
3. Under Calculations, click on "Fields, Items, & Sets" ->Calculated Field
4. Type "Cal1" or any other name which does not exist in pivotfields
5. Formula ='Total Login Hours' - 'Total Break Time'
6. Hit OK

 

[KishorKK]

Thanks a lot . You are really amazing. Nice to interact with you..!!