# Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one month

1. ## Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one month

SOLVED I am a nurse and trying to determine an easier way to calculate days covered under Medicare so I an not doing this manually each month. I am also new to excel, so I will try to be as detailed as I can. Thank you for any help.

Thank you so much,
Una

1. NEED FORMULA FOR:
IF A14=A13 AND IF H14 = 07. Unscheduled THEN E14 WILL BE 7DAYS LESS THAN D14 THEN F13 WILL BE ONE DAY LESS THAN E14

2. NEED FORMULA FOR:
J AND K TO INCLUDE ONLY THE FEB DATES FROM E11 AND F 11

2. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Hi
please post on the forum. External links might not be secure - Thx

3. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Hi and welcome to the forum

Not every-one can access file-hosting sites like the 1 you used (company fire-walls etc), please upload your sample file here...
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

4. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

How do I do that? - thanks

5. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Thank you - the file should be attached

6. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Thanks

You didnt say what you want to return in E14 if those conditions are not met? In the mean time try this for E14...
=IF(AND(A14=A13,LEFT(H14,3)="07."),D14-7,"")
and this for F14...
=E14-1

edit:..." J AND K TO INCLUDE ONLY THE FEB DATES FROM E11 AND F 11"

what do you want shown if the month is not Feb in 1 of the 2 cells?
what do you want shown if the month is not Feb in both of the 2 cells?

7. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Thank you.

This is what it I get when I put your formulas in -: 41325 and 41324. The dates in E14 & F14 are dependant on the type of assessment in H. We rarely have this odd 07. unschdeuled assessment type but it covers 7 days back to the previous assessment (crazy!) If you look at how the other types calculate - is there any way to put a formula in just when I come across this ( 07. Unscheduled ) assessment. even just cut and paste when I need it. Thank you for your help.
Una

8. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

the answers you get are dates, you justy need to format them as dates (in excel, a date is actually a 5 number value)

try this in E14...=IF(AND(A14=A13,LEFT(H14,3)="07."),D14-7,you're-regular-formula here)
and this in F14...=IF(AND(A14=A13,LEFT(H14,3)="07."),E14-1,you're-regular-formula here)

9. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

We only bill on a monthly basis, 2/1/13 - to 2/28/13 but assessment types can overlap weeks so...

what do you want shown if the month is not Feb in 1 of the 2 cells? - the assessment type (H) as in H11 through H16 covers 1/19 - to 4/28. I wanted J & K to include only the month I am billing 2/1 to 2/28. I will be printing out another months assessments for billing 3/1 to 3/31, which will include some of the above and then apply the formulas.

I hope I explained this convoluted process.

10. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

try this, in J11, copied down and across (to K11)...
=IF(MONTH(E11)=MONTH(TODAY())-1,E11,"")

This will take the current month (3) less 1 month (2) and compare it with the month in E11

11. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

That works great for the dates -thank you!!!!

So close on the other one ... E 14 IS 2/20/13 SHOULD BE 2/21 AND F14 IS 2/19/13 SHOULD BE 3/19 - WHAT DO YOU THINK? THANKS.

12. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

I think I may be pasting the formula incorrectly - I'll keep tryng - I can not thank you more!

13. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

=IF(AND(A14=A13,LEFT(H14,3)="07."),D14-7,you're-regular-formula here)

Is this the correct way to add "you're regular formula" :

=IF(AND(A14=A13,LEFT(H14,3)="07."),D14-7,IF(A11=A10,F10+1,C11)

thx

14. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Originally Posted by UNAB
So close on the other one ... E 14 IS 2/20/13 SHOULD BE 2/21 AND F14 IS 2/19/13 SHOULD BE 3/19 - WHAT DO YOU THINK? THANKS.
If the criteria are met (they are) then E14 is based on D14-7...D14 is 2/27/2013, so 2/27/2013-7 = 2/20/2013, not 2/21...that would be D14-6.

fir F14, I may have mis-read what you said, or was it a typo?...
"IF A14=A13 AND H14 = 07. Unscheduled THEN E14 WILL BE 7DAYS LESS THAN D14 THEN F13 WILL BE ONE DAY LESS THAN E14"

reply for post #13 - yes that looks about right, test it and make sure though

15. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

A14=A13 AND H14 = 07. Unscheduled THEN E14 WILL BE 7DAYS LESS THAN D14 THEN F13 WILL BE ONE DAY LESS THAN E14"

The above is what I am looking for because an 07 Unscheduled will cover 7 days back from the 2/27 (D14) & overrides the start of the 30day coverage - goes back 7 days and then covers to the end of the 30 day coverage F14. (Medicare is crazy) That is why the F13 should be one day less than E14.

Notes:
01. 5-day - covers day 1-14
02. 14-day - covers day 15-30
03. 30-DAY - covers day 31-60
07. Unscheduled (any time) - covers 7 days back from the date of the last ARD to the # of total days the last ARD covered.
04. 60-DAY - covers 61-90
05. 90-DAY - covers 91-100

Do you think I should just keep the 07 's out and do them separatey?

Thanks

16. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

If there is a rule on what needs to happen when 07. is encountered, then we can handle it (I think we have?) As far as the F13 1 day less than E14...

==IF(AND(A14=A13,LEFT(H14,3)="07."),F14-1,your-other-formula)

17. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

I think it works, I will try in again in the am. Thank you - your dog is cute - have a great evening,

Una

18. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

Let me know how you make out. Yes she is very cute...and very old, she is over 15 years old

19. ## Re: Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one mo

WORKS WELL!!! Thanks so much for your help.

Have a great week.
Sincerely,
Una

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1