Still need help with formula
 

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)
Still need to make "10" a part of this equation. Everytime I change one
thing something else changes. And Allen, please, I am using my brain but I'm
just not as smart as you guys in Excel. Thanks in Advance!
If < 90, thats ok!
If 90 < 12,31)-B16)*0.0274)+0.5, thats ok!
What I also need: If 12,31 of that same year then "10"
"B16" is a fixed date that never changes

Still need help with formula
 

The spec is not clear.

You want

if TODAY is within 90 days of B16, then return 0?

if TODAY is less than 31st Dec of B16 year, then return what exactly?

Then if TODAY is greater than 31st Dec of B16 year, then return 10?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)
Still need to make "10" a part of this equation. Everytime I change one
thing something else changes. And Allen, please, I am using my brain but
I'm
just not as smart as you guys in Excel. Thanks in Advance!
If < 90, thats ok!
If 90 < 12,31)-B16)*0.0274)+0.5, thats ok!
What I also need: If 12,31 of that same year then "10"
"B16" is a fixed date that never changes

Still need help with formula
 

1) YES, a zero to your first question.

2) If Today()-B16 is greater than 90, but less than Dec 31 of the same year
then 0.0274 * B16, (from the date in B16 to Dec 31st only)

3) Yes to your third question

*What this is and why it's such a pain... B16 is a hire date.
*If employed less than 90 days you have "0" Vacation days.
*If employed 90 days then you are prorated ( 0.0274 per day) from your hire
date B16 till the end of the year)
*After Dec 31 of that year it's no longer prorated and you have "10" days,
starting Jan 1 of the next year.
Thanks for your help, I just can't get it!!!

"Bob Phillips" wrote:

The spec is not clear.

You want

if TODAY is within 90 days of B16, then return 0?

if TODAY is less than 31st Dec of B16 year, then return what exactly?

Then if TODAY is greater than 31st Dec of B16 year, then return 10?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)
Still need to make "10" a part of this equation. Everytime I change one
thing something else changes. And Allen, please, I am using my brain but
I'm
just not as smart as you guys in Excel. Thanks in Advance!
If < 90, thats ok!
If 90 < 12,31)-B16)*0.0274)+0.5, thats ok!
What I also need: If 12,31 of that same year then "10"
"B16" is a fixed date that never changes

Still need help with formula
 

Does this do whatyou are after?

=IF(TODAY()-B16<90,0,IF(AND(TODAY()-B1690,TODAY()<DATE(YEAR(B16),12,31)),INT((DATE(YE AR(B16),12,31)-B16)*0.0274)+0.5,10))

Hope that helps?

Regards

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571796

Still need help with formula
 

I think you want this

=IF(B16="","",IF(YEAR(B16)<YEAR(TODAY()),10,IF(TOD AY()-B16<90,0,(TODAY()-B16
)*0.0274)))

And 10 days a year! What are you running, a slave camp?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
1) YES, a zero to your first question.

2) If Today()-B16 is greater than 90, but less than Dec 31 of the same
year
then 0.0274 * B16, (from the date in B16 to Dec 31st only)

3) Yes to your third question

*What this is and why it's such a pain... B16 is a hire date.
*If employed less than 90 days you have "0" Vacation days.
*If employed 90 days then you are prorated ( 0.0274 per day) from your
hire
date B16 till the end of the year)
*After Dec 31 of that year it's no longer prorated and you have "10" days,
starting Jan 1 of the next year.
Thanks for your help, I just can't get it!!!

"Bob Phillips" wrote:

The spec is not clear.

You want

if TODAY is within 90 days of B16, then return 0?

if TODAY is less than 31st Dec of B16 year, then return what exactly?

Then if TODAY is greater than 31st Dec of B16 year, then return 10?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)
Still need to make "10" a part of this equation. Everytime I change
one
thing something else changes. And Allen, please, I am using my brain
but
I'm
just not as smart as you guys in Excel. Thanks in Advance!
If < 90, thats ok!
If 90 < 12,31)-B16)*0.0274)+0.5, thats ok!
What I also need: If 12,31 of that same year then "10"
"B16" is a fixed date that never changes

Still need help with formula
 

Sorry that should be

=IF(B16="","",IF(YEAR(B16)<YEAR(TODAY()),10,IF(TOD AY()-B16<90,0,(DATEVALUE("
31-Dec")-B16)*0.0274)))

and where does the .5 come into it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
1) YES, a zero to your first question.

2) If Today()-B16 is greater than 90, but less than Dec 31 of the same
year
then 0.0274 * B16, (from the date in B16 to Dec 31st only)

3) Yes to your third question

*What this is and why it's such a pain... B16 is a hire date.
*If employed less than 90 days you have "0" Vacation days.
*If employed 90 days then you are prorated ( 0.0274 per day) from your
hire
date B16 till the end of the year)
*After Dec 31 of that year it's no longer prorated and you have "10" days,
starting Jan 1 of the next year.
Thanks for your help, I just can't get it!!!

"Bob Phillips" wrote:

The spec is not clear.

You want

if TODAY is within 90 days of B16, then return 0?

if TODAY is less than 31st Dec of B16 year, then return what exactly?

Then if TODAY is greater than 31st Dec of B16 year, then return 10?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)
Still need to make "10" a part of this equation. Everytime I change
one
thing something else changes. And Allen, please, I am using my brain
but
I'm
just not as smart as you guys in Excel. Thanks in Advance!
If < 90, thats ok!
If 90 < 12,31)-B16)*0.0274)+0.5, thats ok!
What I also need: If 12,31 of that same year then "10"
"B16" is a fixed date that never changes

Still need help with formula
 

Yes!!! Thank you so much, Thats it!

"mr_teacher" wrote:


Does this do whatyou are after?

=IF(TODAY()-B16<90,0,IF(AND(TODAY()-B1690,TODAY()<DATE(YEAR(B16),12,31)),INT((DATE(YE AR(B16),12,31)-B16)*0.0274)+0.5,10))

Hope that helps?

Regards

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571796

Still need help with formula
 

Yes!!! I can get it to work from there! To answer your question the INT 0.5
was used to round if a half day. And no thats not all the days we get, thats
just within the first year. I have a VLookup for the rest. Thanks again so
very much for your help!!!

"Bob Phillips" wrote:

Sorry that should be

=IF(B16="","",IF(YEAR(B16)<YEAR(TODAY()),10,IF(TOD AY()-B16<90,0,(DATEVALUE("
31-Dec")-B16)*0.0274)))

and where does the .5 come into it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
1) YES, a zero to your first question.

2) If Today()-B16 is greater than 90, but less than Dec 31 of the same
year
then 0.0274 * B16, (from the date in B16 to Dec 31st only)

3) Yes to your third question

*What this is and why it's such a pain... B16 is a hire date.
*If employed less than 90 days you have "0" Vacation days.
*If employed 90 days then you are prorated ( 0.0274 per day) from your
hire
date B16 till the end of the year)
*After Dec 31 of that year it's no longer prorated and you have "10" days,
starting Jan 1 of the next year.
Thanks for your help, I just can't get it!!!

"Bob Phillips" wrote:

The spec is not clear.

You want

if TODAY is within 90 days of B16, then return 0?

if TODAY is less than 31st Dec of B16 year, then return what exactly?

Then if TODAY is greater than 31st Dec of B16 year, then return 10?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard" wrote in message
...
=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)
Still need to make "10" a part of this equation. Everytime I change
one
thing something else changes. And Allen, please, I am using my brain
but
I'm
just not as smart as you guys in Excel. Thanks in Advance!
If < 90, thats ok!
If 90 < 12,31)-B16)*0.0274)+0.5, thats ok!
What I also need: If 12,31 of that same year then "10"
"B16" is a fixed date that never changes

Still need help with formula
 

Glad you got it to work!

Thanks for the feedback too!

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571796