• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF statement using dates

G

Ginaf

New Member
Hello,

I would like to insert a formula in Column G that states: If the the last hire date (column E) is greater than 09/30/14, annualize the RVU's in column F based on the number of days worked during the period 10/01/14 to 04/30/15. If the last hire date is before 10/01/14, then annualize column F using the formula (F/7 months * 12 months).
 

Attachments

Hi Ginaf

for me this is not clear

"annualize the RVU's in column F based on the number of days worked during the period 10/01/14 to 04/30/15"

It's not clear how you want this to be calculated
 
I'm sorry. Let me try again. If someone was hired on Jan 1, 2015 and their April 2015 YTD RVU's were 519.06. I would annualize April 2015 RVU's as follows: 519.06/4 months X 12 months or 1,557.18. Does that clarify?

Thank you for your help.
Gina
 
Hi in attachement I set up a formula but there might be needed a modification. Excel evaluates month april - januar as 3 months, and I noticed that previously with this date 17.2.2015 you had there in denominator 2,5 months . SO if you need further improvement on formulae I would need specification what constitutes x months difference between 30.4.2015 and hire date
 

Attachments

tomas said:
Hi in attachement I set up a formula but there might be needed a modification. Excel evaluates month april - januar as 3 months, and I noticed that previously with this date 17.2.2015 you had there in denominator 2,5 months . SO if you need further improvement on formulae I would need specification what constitutes x months difference between 30.4.2015 and hire date
Click to expand...

Hello,

Can you tell me what the 41912 number represents in your formula e3>41912? It may help if I explain the theory behind what I am doing. Our hospital owns a physician practice. RVU's or relative value units represent productivity. Our fiscal year begins on Oct 1 and ends on Sept 30. I am trying to budget each physician's productivity for fiscal year 2016 based on 2015 activity. I have their actual RVU's as of April 2015 year to date (column F) which represents seven months of data. I am trying to annualize their April 2015 RVU's based on hire dates. If a physician is hired before Oct 1, 2014 then they will have have a full year of productivity. If they are hired after October 1, 2014, they will not have a full year of productivity which is why I need to annualize actual April 2015 based on hire dates. I hope this illustrates more clearly what I am trying to do. Sorry for any confusion. Thank you again tomas.
 
Hi

41912 i as serial number for a date 30/09/2014. I could put in the cell date but was not sure about correct formatting . You can try it out if you put in a cell 41912 and change number format to date.

Your explanation is clear. I put in the cell e9 the key part of formula and you can see how it calculates and play around with dates.

But let's look at month difference between 17.2.2015 and 30.4.2015.

1. my formula calculates that as 2 month difference.

2. It can be modified to make it 3 months difference

3. Sure to get the best approximation I wouldn't divide it by month difference but day difference and then multiply by 365.

So I can modify the formulae for you but you need to specify rules so I know to set up formulae for that denominator
 

Attachments

tomas said:
Hi

41912 i as serial number for a date 30/09/2014. I could put in the cell date but was not sure about correct formatting . You can try it out if you put in a cell 41912 and change number format to date.

Your explanation is clear. I put in the cell e9 the key part of formula and you can see how it calculates and play around with dates.

But let's look at month difference between 17.2.2015 and 30.4.2015.

1. my formula calculates that as 2 month difference.

2. It can be modified to make it 3 months difference

3. Sure to get the best approximation I wouldn't divide it by month difference but day difference and then multiply by 365.

So I can modify the formulae for you but you need to specify rules so I know to set up formulae for that denominator
Click to expand...


I agree i would be best to modify the formula so that it calculates based on number of days versus number of months.
 
tomas said:
Gina

I modified the formula so it uses days.

David's suggestion is also reasonable to apply in case your physicians are off at weekends and holidays but the difference would be negligable
Click to expand...

David Evans said:
You can also use the Function NETWORKDAYS to calculate the number of whole workdays between two supplied dates, and this function can be customized to reflect holidays etc. See https://support.office.com/en-ca/article/NETWORKDAYS-function-48e717bf-a7a3-495f-969e-5005e3eb18e7
Click to expand...
Thank you! I appreciate it!
 
You must log in or register to reply here.
Back
Top