thatsmewanda

New Member
Joined
Dec 17, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Okay, I am beginner/intermediate Excel user for work. Working with Excel 2010.
I have a thought process in my head for what I want to happen, but cannot figure out how to execute it. I am tracking employee attendance over a 12 month rolling calendar. Here is my spreadsheet;

1608221639714.png


Basically I have a tab for each employee, for the year. I have on line for "code" meaning how we track why they were absent, and a second line for the amount of hours they were absent for. I also have a total column for the year.
I inherited this spreadsheet from someone and I think it was just a basic template they got offline.
So if I were using just a standard year this functions fine, but I don't want to have to make a new calendar every year and then look between multiple calendars when trying to count how many absences employees have over a 12 month rolling period.

I want to be able to view it all on this one calendar continuously. I have thought about using countif, today, and the if function etc., but I can't seem to make it work not in practice or concept.
I basically want one of 2 things. The total column to add a 1 if the hours are more than 4 and if it was within the last 365 days. This would mean I have still to go through and still delete the hours portions of the sheet if it has been more than 365 from that date. Which is fine.

More ideally I would like to be able to add the number in the hours line, have it counted in the total as 1 if it is more than 4 hours, and then once it has been 365 days that number cell returns to blank or zero. This way I don't ever have to delete anything, it will automatically only calculate what dates and hours matter because they occurred in the last 365 days.

Again, compared to a lot of the posts I have read through here I have rudimentary knowledge of Excel, I don't know if this is possible, or if there is a better way to get the result I am looking for? O maybe if there is a different template I can download and personalize that would give me the results I am looking for?
I do have the developer tab activated, and know how to use it to basically auto sort. Also I have some function knowledge, and some formatting knowledge.

Any ideas or help?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
thatsmewanda

If you are looking at having data from 2 partial years, where would the second year go? Would you add another year under the one you have shown or would you have it on another sheet? And following on from that, how many years will you be having data for? Will you just keep adding years or will you be deleting old years?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,241
Messages
5,595,021
Members
413,960
Latest member
ikkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top