If / Not If / Vlookup

[sbworcs]

I have to try and use excelt for absence reporting.

I have one tab of data with a list of annual leave (for example) with the following data

Employee Number Start Date End Date
Employee 1 1st January 2nd January
Employee 1 1st March 3rd March
Employee 2 2nd February 3rd February.

The main tab has employee name in column a then dates as headers for the remainder of the columns 1st January - 31st December (eg.)

Is there a way to put a mark (e.g. YES) agains the relevant cell to show that someone was off on a particular date?

Thanks


Stephen

[Hang Glider]

Stephen,
I'm close - I've got your start and end dates working. Not sure how to fill in the dates inbetween yet.

have a look, and perhaps others will have an easier solution or will be able to complete this one. (I haven't given up, but am stumped at the moment)

to get the check mark, my "if" formula uses Alt + 129 for the umlaut "ü" and then formatting the area with the wingdings font results in a nice check mark.

[Hang Glider]

And, closer still....

Stephen
for this to work, You'll need to do a bit of math on the tab with start/end dates. I've added a column titled "total days" which is the total number of days absent.

The formula is full of nested if's and vlookups, but I think it gives you what you want. Transpose to your sheet carefully, as missed commas and parentheses will certainly render it useless.

and - I did not account for weekends.

best of luck!



if this will work for you, please edit your original post, edit the Heading, change it to [SOLVED] and feel free to click on the star at the bottom left of this post...