Still need help with formula

[Richard]

=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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> =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
>

[Richard]

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" <[email protected]> wrote in message
> news:[email protected]...
> > =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
> >
>
>
>

[mr_teacher]

Does this do whatyou are after?

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

Hope that helps?

Regards

Carl

[Bob Phillips]

I think you want this

=IF(B16="","",IF(YEAR(B16)<YEAR(TODAY()),10,IF(TODAY()-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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > =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
> > >
> >
> >
> >

[Bob Phillips]

Sorry that should be

=IF(B16="","",IF(YEAR(B16)<YEAR(TODAY()),10,IF(TODAY()-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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > =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
> > >
> >
> >
> >

[Richard]

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

"mr_teacher" wrote:

>
> Does this do whatyou are after?
>
> =IF(TODAY()-B16<90,0,IF(AND(TODAY()-B16>90,TODAY()<DATE(YEAR(B16),12,31)),INT((DATE(YEAR(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
>
>

[Richard]

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(TODAY()-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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > =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
> > > >
> > >
> > >
> > >
>
>
>

[mr_teacher]

Glad you got it to work!

Thanks for the feedback too!

Carl