Vacation Taken vs Vacation Scheduled

I'm trying to get this spreadsheet to figure out the difference between scheduled vacation and vacation that has already been taken according to the current days date. In other words, if vacation was taken today or prior to today, it should count as taken. If vacation is scheduled for tomorrow or beyond, it should count as scheduled.

I'm attaching my spreadsheet. Excuse the mess.... it's a work in progress. I've put a couple arrows pointing at the fields in which I'm trying to get the formula into.

Thanks!

Hi,

I cant view your sheet correctly on my tablet. But i think =today() will help you a lot.
=today() will show the current date. So vacationdays smaller then =today() already have been taken, vacationdays larger then =today() is sceduled.

This is my first post, i hope it made sense :-)

Wbr, Dinus.

Originally Posted by Lemice

Hello,

You will need something to indicate the time so you can differentiate "Taken" and "Scheduled" in your sample. Your simple SUMIF will just lookup on the name list, if a name is found, then add everything on column AZ in the same column.

What you can do is adding a helper column that indicates the month, then make SUMIF check it also, which will make your file be even more messy. Since you are using Excel 2003, you will need to improvise with SUMPRODUCT instead of SUMIFS.

Edit: here is a sample file in which I have delete all worksheet starting from the 3rd (they are not necessary for the calculation for now, and I just want to illustrate a workaround, and actually I should have deleted the first worksheet too)

If it would greatly help me (or you to help me) to have Excel 2010, I'll get it installed. I was trying to keep from forking over the money for it but I suppose I shouldn't be such a cheapskate... lol

Anyhow... When you say I need something to indicate the time, would the calendar itself not be that reference?
I thought that since the month and days were listed on it, that the formula would somehow refer to it. Of course, I don't know what I'm doing half the time anyways. If not for the kind people on here, I wouldn't have gotten this far on my spreadsheet.

Thanks for helping!

Originally Posted by Lemice

To you, yes, you can indicate which data belong to which calendar, but for a formula you will need an indicator like on the last part of my formula, where I point to a column with a lot of 1, 2, etc.

Don't hesitate if you have any other problems.

Ok, I think I understand. I'll give your formula a try when I can get back to my PC. I'm keeping my fingers crossed! This would be a great help... Thanks!

Originally Posted by Lemice

I might be able to come up with a formula that does not need any extra column, but in order to do that, I have a question: Does the number of row between table of month equals? For example you have fixed 20 rows for each month?

Yes, each month has 20 rows. If I were to add rows, I would always add the same number of rows to each month. That way, each month would always have the exact same amount of rows.

Originally Posted by Lemice

Here is a version that will check the month value, and you won't need any extra column.

Here is the formula, all you have to do is paste it on BH 8 and drag it down / right

Please Login or Register  to view this content.

Similarly, this is for BP 8

Please Login or Register  to view this content.

Drag it down and right.

Note that this one is taking advantage of the fact that there are 20 rows per month, so each time you increase a row, you will have to manually change the formula.

For every 1 row you increase, you will have to change the number 25 in the formula by that amount, making sure that that number will always be your row per month + 5.

That is great..... It is REAL close to being just right. The only thing I can see that it's doing wrong is calculating hours "taken" and hours "scheduled" according to the month rather than the exact day.

For example: If I enter vacation hours for an employee on tomorrow's date, 4/30/13, the sheet is counting that as "taken", rather than "scheduled", even though that day hasn't gotten here yet. Do you know of a way to get it to calculate by the exact day instead of the month?

BTW... I've upgraded to 2010 Excel, if that will help use a different formula altogether.

I've attached a new example sheet for you

So if you really want to even check the day, you might want to add extra columns, make a macro, or change some of your formulas.

The reason the columns change, depending on the day is because if you change the year in my "Year Calendar" sheet, it automatically changes the calendars on all the sheets automatically. This will keep me from building a new workbook each year.


I really appreciate you trying to get it figured out for me. I don't mind adding a helper column but I wouldn't know what all to add to make it work. If you have a suggestion that would work, that would be great. I know you've tried a bunch already, though. If you don't have the time to mess with it any longer, don't worry about it I might could post over on the macros boards and see if someone might could figure it out over there.

Thanks for what you've done!

Originally Posted by Lemice

But I don't like leaving things unsolved / undone.

Here is your sample with a bit of changing on layout, and some helper columns.

Let me know if this is what you are looking for.

How on earth did you do that? lol! Geez! I'm sending you a message...

Originally Posted by Lemice

Glad that I could help.

If you have found a fitting solution to your problem, please mark the thread as [SOLVED] using the Thread tools right above post #1.

And don't hesitate if you have any other question.

Have a great day.

Lem... I tested it all out and can't find any errors. This has been a great help to me. Thanks for all your help! Now, on to some more fun stuff with it... lol. I'm sure you'll see me post about it again very soon. I've got some more that I'm sure I won't be able to figure me out on my own.