Excel 2013: Extending Attendence sheet

Hi: I have created Attendence sheet in Excel in which there is mention employee name, in time , out time and total hours and our office is open 24*7*365 so there is different shift working.Problem which is below.

1:- I want to add 3 columns one is ."Total days working, Total hours" need in the end of Month.

2:- How we can able to calculate it total days working or total hours. When i tried to use formula in attendence sheet it won't work.

3:- How colours will be set for PL, CL, week off so that if i mention PL then colour automatic change so on.
Sanjeev jhaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
General guidance would suggest:

Learn that subtracting dates with times is often handled as Newest Date/Time - Oldest Date/Time.  The difference is a decimal number.  The days are to the left of the decimal and the hours to right of the decimal.  If you want the total hours, you would use: (Newest Date/Time - Oldest Date/Time)*24  to find the number of hours.

If you desire to change format based on a formula, that option is available in Conditional Formatting.  You would highlight the area that needs formatting, Click on Conditional Formatting, Select New Rule...
In the window that opens, select the last option of Use a formula to format cells

The Topic Advisor is correct that more specific help can be provided with a clear example.
0
Sanjeev jhaAuthor Commented:
Thank you very much:
I am posting two sheets one is attendence sheet which we use daily for maintaining attendance.

2: Second sheet which I need to provide my boss in the last of every month. I do it manually which took much time, need some formula so that it could be done fast and can save time.
January-2015-CCTV.xlsx
Hours-worked.xlsx
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
A very simple approach:

Column F should be
  =IFERROR(E2-D2+1-INT(E2-D2+1),)
Set H for "Total Days" to
  =COUNTIF(D2:D32,"<>" & "Week Off")
and I for "Total Hours" to
  =SUM(F2:F32)

If you keep the accounting sheet at 31 days a month, even for February etc., You do not need to change anything.

To color individual cells depending on the content, just use Conditional Formatting. Compare against "PL" etc., and change the cell background color for each case.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjeev jhaAuthor Commented:
Ressolved the issue
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.