Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |