Need help counting vacation hours for partial year

[mharden]

Hi everyone:I have an issue I've been working on, I've gone through everything I could think of and has come up empty so I'm hoping someone can help.At my company we track vacation time on a fiscal year (instead of calendar year) basis and you can't carry over vaction. So if you were hired in May, your vacation goes from May 2013 through May 2014 and whatever isn't used is lost and you start anew.There's a spreadsheet that tracks the information for the calendar year, but I need to calculate the vacation taken from hired date through the end of the year. If a person's hire date is in May I can easily calculate the vacation taken from January through May. I'm trying to figure out a way to calculate vacation taken from June through December without the figures from January through May.2013 |---------------------------------|---------------------------------------| Jan May Dec 16 hrs taken 8 hrs taken (this is counted in 2012 totals) (this needs to go for 2013 totals, along with Jan-May 2014); max hrs allowed is 40 per fiscal year.As I've tried everything I could think of any help is greatly appreciated.Thanks,Melody

 

[s hal]

Do you have a hire date for each person? This could be a field, and then you could produce a prior year to current year value based upon the month and day of the hire date and sum vacation earned between those dates for the respective year (however it is you're wanting to do it).

And just in case that isn't clear, say the hire date is in cell A1, I would do a formula such as the following to make it from 2012 to 2013:
=value(text($A$1,"m")&"\"&text($A$1,"d")&"\"&2012) - this should return a date of m\d\yyyy for you to use logic checks against

Hope this helps!

 

[mharden]

Thanks s hal. I do have a cell for the hire date so I'll give it a try and see what happens.