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

MY FILE CAN BE DOWNLOADED FROM RAPIDSHARE - http://my.rapidshare.com/UNAB/19016


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

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

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.

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?

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)

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

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

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)

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