• 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.

FORMULAS THAT DISPLAY DATES COVERED FOR PERIOD BASED ON ADMITTANCE

U

UNAB

New Member
I am a nurse NEW TO THIS site and with a new company that does manual data for determining covered dates of medicare service. I have some formulas in this problem, but could someone please help – I have attached links to files I uploaded to Skydrive. I need the following formulas for spreadsheet # 2: ANY HELP IS SO APPRECIATED.


General Information – ALL BASED ON AN ADMITANCE DATE:

5 DAY ASSESSMENT (ARD) COVERS DAY 1-14

14 COVERS DAY 15-30

30 COVERS DAY 31-60

60 DAY COVERS DAY 61-90

90 DAY COVERS DAY 91-100

1. IF I7= 01. 5-DAY I WANT TO ADD 14 DAYS TO ADMIT DATE E7 BUT WANT IT TO DISPLAY FROM THE ADMIT DATE TO THE 14TH DATE AS A DATE RANGE LIKE 1/19/13 - 2/1/13. IN YELLOW BOX

2. THEN IF I7 = 02. 14-DAY I WANT TO ADD 16 DAYS TO THE 2/1/13 ABOVE AND DISPLAY AS 2/2/13 TO 2/17/13

3. THEN IF I7 = 03. 30-DAY I WANT TO ADD 30 DAYS TO THE 2/17 AND DISPLAY AS 3/18/13 TO 3/19/13


4. THEN IF I7 IS 04. 60-DAY I WANT TO ADD 60 DAYS TO THE DATES AS ABOVE 3/20 TO 4/18


https://skydrive.live.com/?id=7FB3040C6AEBB522!111&cid=7fb3040c6aebb522#!/edit.aspx?cid=7FB3040C6AEBB522&resid=7FB3040C6AEBB522%21114&app=Excel


https://skydrive.live.com/?cid=7fb3040c6aebb522#!/edit.aspx?cid=7FB3040C6AEBB522&resid=7FB3040C6AEBB522%21117&app=Excel


THANK YOU AGAIN!!!!
 
I don't think those links work, so I am reposting


https://skydrive.live.com/#!/edit.aspx?cid=7FB3040C6AEBB522&resid=7FB3040C6AEBB522%21114&app=Excel


https://skydrive.live.com/#!/edit.aspx?cid=7FB3040C6AEBB522&resid=7FB3040C6AEBB522%21117&app=Excel


This is the first time I have used Skydrive as well, super newbie here!!! Also, sorry for the caps, not yelling at all.
 
Hi ,


The links don't appear to work ; can you use any other file-sharing website of your choice , such as RapidShare , GoogleDocs , Hotfile , DropBox , SpeedyShare , and upload your file there.


Narayan
 
I tried RapidShare - hope this works -thank you!!!!


Una


https://rapidshare.com/files/1237617047/SPREADSHEET%201.xlsx


http://rapidshare.com/files/3385884688/SPREADSHEET%202.xlsx
 
Hi Una ,


Sorry , but when I click on this link , I get the following message :


Download permission denied by uploader


You need to give others permission to access and download your file and then post this access link here.


Narayan
 
Hi Narayan -


Please bear with me, I am trying to determine how to give permission. Thanks.


Una
 
Hi Una ,


Yes , this link works. However , it is late at night now , and I'll get back on this only tomorrow morning.


Narayan
 
Hi Unab,


Is Spreadsheet2 is also dependent/linked or in any relationship with Spreadsheet1!!

Is Date in E & F are actually Date.. or it was TEXT.. as my systemn is in DMY format..!!


Until Narayan gives a dependable answer.. Can you please try below for me..

for Spreadsheet1:

* In G7 write formula as

Code: 
=IF(A7=A6,H6+1,E7)

* In H7 Write Formula as

=G7+LOOKUP(I7,{"01. 5-day",14;"02. 14-day",16;"03. 30-day",30})-1


Drag the same to down..


BTW, I am assuming.. Provided Date are E & F are actually Date.. and your system can read it correctly..


Regards,

Deb
 
I TRIED THAT - WOW WOW AMAZING - THANK YOU - THAT WORKS!!!!!! WHAT IF I ADDED ANOTHER COLUMN ( I ) TO LIMIT THE CALCULATION TO THE END OF ANY GIVEN MONTH - SO THAT "I" COLUMN WOULD GO TO FEB 28 OR MARCH 31, DEPENDING ON WHAT MONTH OF THE YEAR.


THANK YOU SO MUCH!!!


UNA
 
Hi Una ,


I am not able to understand what you want in the columns G and H ; if you say that Deb's formula is what you were looking for , then it's fine with me.


Suppose a person A is having two entries , one after the other ; is it necessary that these dates should overlap with one another ? Just for example , suppose these were two entries for the same person :

[pre]
Code: 
JOE     RUA10     02-06-2013     01. 5-day
JOE     RUA20     02-26-2013     02. 14-day
[/pre]
Can you say what should be displayed in columns G and H against these two entries ?


Secondly , regarding Spreadsheet 2 , I can't understand what you want. Can you specifically say which cells should be filled in with formulae , and what the result should be in those cells ?


Narayan
 
Good day Narayan - A person can have multiple dates associated with a admission (all based on the date of entry to the hospital). Doing this manually - this is what is displayed in G and H 2/6 - 2/19 (I tried to edit the post and the below all runs together)


RUG ADMIT FROM (G) TO (H)

JOE RUA10 02-06-2013 02-06-2013 02/19/13 (14 DAYS) 01.5-DAY

JOE RUA20 02-06-2013 02/20/13 03/07/13 (16 DAYS) 02.14-DAY

JOE RUA30 02-06-2013 03/8/13 04/08/13 (30 DAYS) 03.30-DAY

JOE RUA40 02-06-2013 04/9/13 5/8/13 (60 DAYS) 04.60-DAY

JOE RUB50 02-06-2013 05/09/13 05/18/13 (10 DAYS) 05.90-DAY


JOE CAN HAVE UP TO 100 DAYS. THE 01.5-DAY, 02.14-DAY AND SO ON DETERMINES THE DAYS. THE "FROM" AND "TO" STARTS FROM THE ADMIT DATE.


THE ARD DATE HAS NO EFFECT ON G or H


DEBS FORMULA DOES WORK, BUT HERE IS ANOTHER QUESTION - WE BILL FROM THE FIRST OF THE MONTH TO THE LAST DAY OF THE MONTH - IS THERE A WAY TO HAVE THE DATES SHOW ONLY THE START OF THE MONTH AND THE END OF THE MONTH FOR EXAMPLE:


JOE RUA20 02-06-2013 02/20/13 03/07/13 (16 DAYS) 02.14-DAY

WOULD LOOK LIKE THIS:

JOE RUA20 02-06-2013 02/20/13 02/28/13


***(2/20 TO 2/28 COULD DEFINATELY BE CALCULATED IN ANOTHER TWO COLUMNS AS WELL)


TO BE CLEAR REGARDING SPREAD SHEET 2 - I WAS INCLUDING SPREAD SHEET 2 TO SHOW SOME OF THE FORMULAS THAT I USE FOR THE DAYS COVERED - NO NEED TO CHANGE ANY THING THERE.


THANK YOU KINDLY FOR YOUR HELP.


UNA
 
Hi Una ,


Sorry , but I am still confused ; to take your own data :

[pre]
Code: 
JOE RUA10     02-06-2013     02-06-2013      02/19/13    (14 DAYS) 01. 5-DAY
JOE RUA20     02-06-2013     02/20/13        03/07/13    (16 DAYS) 02.14-DAY
JOE RUA30     02-06-2013     03/8/13         04/08/13    (30 DAYS) 03.30-DAY
JOE RUA40     02-06-2013     04/9/13          5/8/13     (60 DAYS) 04.60-DAY
JOE RUB50     02-06-2013     5/09/13         05/18/13    (10 DAYS) 05.90-DAY
Now , you say that the second entry should actually show :

[pre][code]JOE RUA20     02-06-2013     02/20/13        02/28/13    (16 DAYS) 02.14-DAY
So what happens to the days from 03/01/2013 till 03/07/2013 ? Where will they appear ? Will the next entry now change from what is shown above :

JOE RUA30     02-06-2013     03/8/13         04/08/13    (30 DAYS) 03.30-DAY
[/pre]
to

JOE RUA30 02-06-2013 03/01/13 03/31/13 (30 DAYS) 03.30-DAY[/code][/pre]
Narayan
 
I am writing in upper case as it is easier with all this information.


YES!!! YES, BECAUSE WE CAN ONLY BILL FOR ONE MONTH AT A TIME .... FROM 2/1 TO 2/28 SO IF THE 01.5-DAY DATES COVERS END OF FEB AND BEGINNING OF MARCH - THEN IT IS SPLIT BETWEEN THE TWO MONTHS -


BUT SINCE DEBS FORMULA GIVES ME THE CORRECT NUMBER OF DAYS - COULD TWO OTHER COLUMNS LOOK AT THE DATES IN G AND H AND ONLY GO TO END OF MONTH AND IN THE NEXT MONTH ONLY INCLUDE THE START OF THE MONTH?


THIS IS A VERY COMPLICATED WAY WE BILL HERE AND THAT IS WHY I AM SO HAPPY AND GRATEFUL TO HAVE FOUND THIS SITE.


Thank you for your continued help,

Una
 
You must log in or register to reply here.
Back
Top