Finding employee absence history

[JohnPM]

The return in cell A1 should read the sum of six different cells. These cells
will be identified by a time period over the last six months from today
now()
What strategy (or formula) should I use to make this happen?

What I am looking at is to count the number of days absent in the last six
months of an employee, recorded in separate months.

 

[JP Ronse]

Hi John,

Please add some sample data to your request, we need a view on this to help
you further.

Wkr,

JP

 

[Jacob Skaria]

You havent mentioned how your data is arraned...Explain a bit about that if
possible post sample data/

If this post helps click Yes

 

[WallyWallWhackr]

The return in cell A1 should read the sum of six different cells. These cells
will be identified by a time period over the last six months from today
now()
What strategy (or formula) should I use to make this happen?

What I am looking at is to count the number of days absent in the last six
months of an employee, recorded in separate months.

Take a look at this annual log, and change, use, delete, or whatever as
you wish. It is a trusted Microsoft User submitted template.

http://office.microsoft.com/en-us/templates/TC300070531033.aspx

There is another that has it by weeks:

http://office.microsoft.com/en-us/templates/TC300067601033.aspx

It has some flaws tho that I recently discovered and am working on, but
is worth a look for conceptual aid anyway.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Last 6 months.
http://www.mediafire.com/file/zzqjky3dl03/10_18_09.xlsx

 

[JohnPM]

A1 is the return cell. In my spreadsheet the columns D, G, J, M, P, S, V, Z,
AC etc contain the numbers of days absent in Apr09, May09, Jun09, Jul09,
Aug09, Sept09, Oct09, Nov09, Dec09, Jan, Feb, Mar, Apr, etc. for my employees.

I need to report the absence of the employee over the last six months, in
other words How many days absence did the employee have form Apr09. Next
month I need to have a return of absence from May09. In December I need the
last six months from Jun09. Its a rolling six months.

 

[JohnPM]

Thank you, just a small problem, I use the Excel 2003 version.

 

[JohnPM]

Thank you, this looks halpful, but I now need to dig my heels into pivot
tables.

 

[JohnPM]

Start date 01/08/2009

A B C D E F
G H J K
1 last 6 aug09 sep09 oct09 nov09 dec09 jan10 feb10
mar10
2 employee 01 ?? 2 5 1 0 0
3 4 3

Today the return in B2 should be 7 (Aug09+Sep09)
in Nov09 the return in B2 should be 8 (Aug09+Sep09+Oct09)
in Dec09 the return in B2 should be 8 (Aug09+Sep09+Oct09+Nov09)
in Jan10 the return in B2 should be 8 (Aug09+Sep09+Oct09+Nov09+Dec09)
in Feb10 the return in B2 should be 11 (Aug09+Sep09+Oct09+Nov09+Dec09+Jan09)
in Mar10 the return in B2 should be 13 (Sep09+Oct09+Nov09+Dec09+Jan09+Feb09)
in Apr10 the return in B2 should be 11 (Oct09+Nov09+Dec09+Jan09+Feb09+Mar09)
And so on; rolling six month period...