Calculate total employee attendance

**sans** · 09-05-2012, 06:25 AM

Hello,

I would like to calculate the total attendance of employees and the daily punch in and out time as well. I am encountering two issues: the first is when an employee punches in i.e. today and punches out a few hours after midnight the following day. I tried formatting the result in various ways but can't get the correct result. Also would it be possible to automate the process somehow? You can see the exact format in the attachment below.

Thank you for looking

**TMS** · 09-05-2012, 06:28 AM

Add the date and time together before subtracting:

=(B7+C7)-(B6+C6)

Regards, TMS

**TMS** · 09-05-2012, 06:32 AM

Or better:

Cell F6: =IF(D6="OUT",(B6+C6)-(B5+C5),"")

and copy formula down.

Regards, TMS

**sans** · 09-05-2012, 09:02 AM

Thank you very much! How does the adding of the date and time work? Can I read about it somewhere?

**TMS** · 09-05-2012, 09:36 AM

You're welcome. Thanks for the rep.

A date is a number, the number of days from 01/01/1900. If you change the format of a date cell to general, you'll just see a number. Similarly, a time is also a number, a fraction of a 24 hour period. So, if you add a date and time you get a number with a decimal part. When you subtract a time, say, late today, from a time early tomorrow, you get a negative time ... which Excel isn't happy about. But, if you add the dates and times before the subtraction, you get a positive time value.

Hope that helps.

Regards, TMS