# Need help counting vacation hours for partial year

#### mharden

##### New Member
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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### s hal

##### Board Regular
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
Thanks s hal. I do have a cell for the hire date so I'll give it a try and see what happens.

Replies
0
Views
140
Replies
4
Views
428
Replies
1
Views
389
Replies
1
Views
690
Replies
1
Views
834

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,524
Messages
5,832,261
Members
430,122
Latest member
SeveretTF

### 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.

### Which adblocker are you using?

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

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