Need help counting vacation hours for partial year

mharden

New Member
Joined
Apr 11, 2013
Messages
5
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198
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

New Member
Joined
Apr 11, 2013
Messages
5
Thanks s hal. I do have a cell for the hire date so I'll give it a try and see what happens.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,902
Members
413,950
Latest member
solve22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top