Create custom year

[oskr989]

Hi, is there any way to create a custom year using a formula, for example from May 1, 2010 to April 30,2011.

Thanks in advance!

 

[Peter_SSs]

I think you would need to expand a bit more on just what you would want to achieve.

 

[oskr989]

Thanks Peter, sorry about that.
I have a spreadsheet that tracks vacations. I need to know if a person has taken at least three vacation days within that specific timeframe (from May 1, 2010 to April 30,2011).

 

[Peter_SSs]

Thanks Peter, sorry about that.
I have a spreadsheet that tracks vacations. I need to know if a person has taken at least three vacation days within that specific timeframe (from May 1, 2010 to April 30,2011).

We would need to know how your sheet is laid out andf how we can tell when/if a person has taken a vacation and for how many days.

 

[oskr989]

This is how it looks:

The light-blue cells are the ones with the quantity of days taken.

 

[Peter_SSs]

Posting a screen shot like that will not get you nearly as many helpers as posting sample data directly in your post. My signature block below has 3 ways to do that. The reasons are that ..

a) many people will not bother to go to another screen to look

b) more importantly, your sample data cannot be copied from that image to a sheet to test developing a solution. Most helpers here would rather be trying to actually solve a problem than typing out sample data.

From what I can see, something like this may get you what you want.

=IF(SUMIF(B1:B100,"Vacation",F1:F100)>=3,"At least 3","Less than 3")

 

[oskr989]

Posting a screen shot like that will not get you nearly as many helpers as posting sample data directly in your post. My signature block below has 3 ways to do that. The reasons are that ..

Thanks for the advice and the formula, my apology for not posting correctly, now my concern is this (and I should've specified this from the very begging ). I need a formula that lets me know whether or not a person has taken at least three vacation days in one request, so to speak, the number in the "Enjoy" column (last one in blue)should be at least three or more, all that within the specific timeframe (from May 1, 2010 to April 30,2011).

Here is the code, as requested:

Excel Workbook
	A	B	C	D	E	F
11	0	Vacation			100%	5
12	1	Vacation	5-Mar-10	5-Mar-10	100%	1
13	2	Comp+	1-Apr-10	2-Apr-10	100%	2
14	3	Comp-	23-Apr-10	23-Apr-10	100%	1
15	4	Comp-	4-Jun-10	4-Jun-10	100%	1
16	5	Personal	10-Mar-10	10-Mar-10	100%	1
17	6	Personal	13-Aug-10	13-Aug-10	100%	1
18	7	Personal	17-Aug-10	17-Aug-10	100%	1
19	8	Vacation	13-Dec-10	24-Dec-10	100%	9
20	9	Vacation	19-Nov-10	19-Nov-10	100%	1
21	10	Personal	4-Feb-11	4-Feb-11	100%	1
EE01

Excel 2007

 

[Peter_SSs]

Try something like this. This wouldn't be foolproof because there could be complications if a vacation was taken that started before the end of a 'year' but ended in the following 'year'.

For example, if the only Vacation taken was say 28-Apr-10 to 3-May-11, from what I can see of your sample this leave would probably show '4' in column F. However, without looking at a calendar, it would be difficult to work out if that leave included 3 daya in April or not.

I also don't see how 13-Dec-10 to 24-Dec-10 gives 9? Apart from a weekend is there another holiday in that period?

Excel Workbook
	A	B	C	D	E	F	G	H	I
11	0	Vacation			100%	5		Start	1/05/2010
12	1	Vacation	5-Mar-10	5-Mar-10	100%	1		End	30/04/2011
13	2	Comp+	1-Apr-10	2-Apr-10	100%	2		>= 3 days at once?	Yes
14	3	Comp-	23-Apr-10	23-Apr-10	100%	1
15	4	Comp-	4-Jun-10	4-Jun-10	100%	1
16	5	Personal	10-Mar-10	10-Mar-10	100%	1
17	6	Personal	13-Aug-10	13-Aug-10	100%	1
18	7	Personal	17-Aug-10	17-Aug-10	100%	1
19	8	Vacation	13-Dec-10	24-Dec-10	100%	9
20	9	Vacation	19-Nov-10	19-Nov-10	100%	1
21	10	Personal	4-Feb-11	4-Feb-11	100%	1
22
Vacations

 

[oskr989]

I also don't see how 13-Dec-10 to 24-Dec-10 gives 9? Apart from a weekend is there another holiday in that period?

The actual holiday was 24-DEC-10, Christmas Eve, the person who wrote that, forgot it (although I am the "administrator", that spreadsheet is being used by several users, so I am not in control of what they put).

Regarding your formula, thank you very much, I'll give it a try.