+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    Thumbs up 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
    Last edited by UNAB; 03-24-2013 at 04:44 PM. Reason: SOLVED

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,135

    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
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #5
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    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?
    Last edited by FDibbins; 03-23-2013 at 03:51 PM.

  7. #7
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    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)
    Last edited by FDibbins; 03-23-2013 at 04:11 PM.

  9. #9
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    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. #11
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #12
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #13
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

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

    Quote Originally Posted by UNAB View Post
    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. #15
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    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. #17
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    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. #19
    Registered User
    Join Date
    03-23-2013
    Location
    Chicago IL
    MS-Off Ver
    Excel 2007
    Posts
    23

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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