Dates and if statement

First, a brief background on me. I am in the Army and am the personal administrative assistant to the battalion Commander, Command Sergeant Major, and Executive Officer. Currently, I am re-designing out battalion personnel roster so all our companies are using the same format. I'm no stranger to code, but cannot figure out some of the more complex stuff I want to be able to calculate.

Ok, here's my conundrum: I am working with two sheets. The first sheet has the list of every soldier, their MOS (job), and the DEROS (date they are scheduled to leave the unit). I have added what is called a "LOSS" column on this sheet that tabulates exactly how many days each soldier has left and shades the cell according to what zone they are in (Green for 90-60 days, Yellow for 60-30 days, Red for less than 30 days).

The second sheet has a list of every MOS and three columns beside each - one for 90 Day Loss, one for 60 Day Loss, and one for 30 Day Loss.

What I need to do is be able to total how many of each type of LOSS (30, 60, 90) each MOS has from the first sheet onto the second sheet.

Does that make sense? I surely hope so, because I've been wracking my brain trying to figure out how to write it. If it requires further explanation to perhaps an example with filled in info, I can try. Thanks in advance, and I hope someone can help me.

-Gunslinger

Hi,

Without seeing your data sheet, may be something along these lines, using SUMPRODUCT?

By the way - please read the rules on posting

http://www.excelforum.com/showthread.php?t=613916

Originally Posted by oldchippy

Hi,

Without seeing your data sheet, may be something along these lines, using SUMPRODUCT?

By the way - please read the rules on posting

http://www.excelforum.com/showthread.php?t=613916

oldchippy,

My apologies for failing to title my thread properly. I will be sure to do so in the future.

Your code is almost what I need. The problem comes in the sense that I am using a cell that tabulates the exact number of days that each soldier has left, not just a flat titling of "30", "60", or "90".

The code I'm using to calculate the days left until a soldier's DEROS is

Please Login or Register  to view this content.

Where the "K" column is the DEROS column and the "#" is the row. Obviously, some of the numbers in this column will be greater than 90, but the only ones I care about and have highlighted with conditional formating are 90 through 0.

P.S. I appear to have a problem with my "number of days left (LOSS)" tabulation. When entering today's date a year from now in the DEROS column, I get 360 in the LOSS column. However, when I chance the DAYS360 to DAYS365 in my coding, I get an error.

I have attached an altered excel file.

Hi,

I've adjust the formulas in the table, does this help? If not it may be better to post a sample zipped sample of your spreadsheet.

He gunslinger try this

Based on the number of days left i assigned it a category (<90,<60,<30)

now that way its much easier to count

Hope this helps.

Hi,

Looks good to me - thanks for the feedback