Formula Support-Duration # of days in a month

Katie,
It's not clear to me how your app knows "which month" when you say how many days occurred in "a month".

hmmm-I guess it doesn't we enter the intake date, the admission date and the discharge date?
But nothing by day-

Katie, For this you will need the field that has the month you are calculating. For example : if someone stayed for 3 months from 15th Jan to 20 April, there are different number of days that person has stayed in each month. 

If you just need to know January (first month from the date admitted) that is fairly simple. Just use the following formula :

ToDays(Min(LastDayOfMonth(Date(Year([BTC Admission Date]),Month([BTC Admission Date]),1)),[BTC Discharge Date]) - [BTC Admission Date])

Katie, if Avinash's answer does not solve your problem, can you give us an example of real data and the real question, just in plain English words, not code.

Okay, I will try that out...
I think you are right tho-I don't have the right data to ask the question.
I really just need to know at the end of each month how many days that client stayed in that month.
But currently only have the date field for admission date and potentially a discharge date (if they discharged) 
I work for a not for profit and I am a "social worker" not a formulas person-but I love this-just having trouble writing formulas...thaks for helping where/when you can...

OK, a clear question in plain English is usually possible to have an answer in Quick Base.

Let's get the question really clear.

You want a report of all Patients who were checked in for at least 1 day last month and how many days they were checked in.  Can you tell me do you count the check in and check out dates at both ends as separate days,  or like a hotel are you just counting "nights

We only count nights (...once I figure this out I need to multiply the days by 24 hours...I add 12 hours for the "last day"...)
each person can stay an unknown duration..

Here is a formula I did  to calculate the storage days in the warehouse last month.   Think of receiving inventory of Clients and then shipping 'em out again! Not so different from rolls of steel coils where the storage fee is per day and they look back to the previous month to cut an invoice for all coils stored last month.  The formula makes use for formula variables for readability

https://help.quickbase.com/user-assistance/formula_variables.html



var date ShipDate = [BTC Discharge Date];

var date ReceivedDate = [BTC Admission Date];


var date FirstDayofPriorMonth = AdjustMonth(FirstDayOfMonth(Today()),-1);

var date LastDayofPriorMonth = LastDayOfMonth($FirstDayofPriorMonth);

var date FirstDayStoredPriorMonth = If(
   $ReceivedDate <= $LastDayofPriorMonth and (IsNull($ShipDate) or $ShipDate >= $FirstDayofPriorMonth), 
                                     Max($FirstDayofPriorMonth, $ReceivedDate));


var date LastDayStoredPriorMonth = If(
             IsNull($ShipDate) and not IsNull($FirstDayStoredPriorMonth), $LastDayofPriorMonth, 
             not IsNull($FirstDayStoredPriorMonth) and $ShipDate >= $FirstDayofPriorMonth, Min($ShipDate,$LastDayofPriorMonth));


var number DaysStoredPriorMonth = If(
    IsNull($ReceivedDate]),0, 
    not IsNull($LastDayStoredPriorMonth), ToDays($LastDayStoredPriorMonth - $FirstDayStoredPriorMonth));


$DaysStoredPriorMonth

Okay-This looks very cool-and at first glance a little over my head. Going to walk the dog and then come back and tackle applying this-I really appreciate your information and will report back with how it goes!!! cheers and have a good night!
Katie

Well copy that formula into a formula numeric field and if we are lucky it will work first try and then I can walk you through it after you walk the dog.

Hey
Sorry took longer than I thought-please know I do not expect a response...
I've been playing and ending up with syntax errors, I played around with the parenthesis... think that may have messed things up? I read and understand how the variable formula helps...
Here is what I did:
var date FirstDayofPriorMonth = AdjustMonth(FirstDayOfMonth(Today()),-1);


var date LastDayofPriorMonth = LastDayOfMonth($FirstDayofPriorMonth);


var date FirstDayStoredPriorMonth = If(
   $BTC Admission Date <= $LastDayofPriorMonth and (IsNull $BTC Discharge Date or $BTC Discharge Date >= $FirstDayofPriorMonth), 
                                     Max($FirstDayofPriorMonth, $BTC Admission Date));

var date LastDayStoredPriorMonth = If(
             IsNull($BTC Discharge Date) and not IsNull($FirstDayStoredPriorMonth), $LastDayofPriorMonth, 
             not IsNull($FirstDayStoredPriorMonth) and $BTC Discharge Date >= $FirstDayofPriorMonth, Min($BTC Discharge Date,$LastDayofPriorMonth));




var number DaysStoredPriorMonth = If(
    IsNull($BTC Admission Date),0, 
    not IsNull($LastDayStoredPriorMonth), ToDays($LastDayStoredPriorMonth - $FirstDayStoredPriorMonth));


$DaysStoredPriorMonth

Can you tell me what happens when you paste my formula into your system, including the first two formula variables that I do not see  in your post above?  I am willing to debug my formual but not your as its difficult to debug a long forula without access to the actual app.

also note that a formula variable name may only contain the letters A-Z or a-z, no spaces, numbers or any other special characters..  Just the 26 letters of the alphabet.

If my formula does not work, please post the formula and the syntax error.

This is the syntax error-Would it mean that I need to change my field name to not have spaces so the first variable works?

Formula syntax error

Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.


var date ShipDate = [BTC Discharge Date];

var date ReceivedDate = [BTC Admission Date];


var date FirstDayofPriorMonth = AdjustMonth(FirstDayOfMonth(Today()),-1);

var date LastDayofPriorMonth = LastDayOfMonth($FirstDayofPriorMonth);

var date FirstDayStoredPriorMonth = If(
   $ReceivedDate <= $LastDayofPriorMonth and (IsNull($ShipDate) or $ShipDate >= $FirstDayofPriorMonth), 
                                     Max($FirstDayofPriorMonth, $ReceivedDate));


var date LastDayStoredPriorMonth = If(
             IsNull($ShipDate) and not IsNull($FirstDayStoredPriorMonth), $LastDayofPriorMonth, 
             not IsNull($FirstDayStoredPriorMonth) and $ShipDate >= $FirstDayofPriorMonth, Min($ShipDate,$LastDayofPriorMonth));


var number DaysStoredPriorMonth = If(
    IsNull($ReceivedDate  ]),0, 
    not IsNull($LastDayStoredPriorMonth), ToDays($LastDayStoredPriorMonth - $FirstDayStoredPriorMonth));


$DaysStoredPriorMonth

On this line here near where the underlining starts, there is an extraneeous closing square bracket to be removed


IsNull($ReceivedDate),0,

It immediately worked!! One last question it is off by one day, so perhaps I do count the last day unless they discharge? Is there a quick fix-or will that undermine the whole formula? 
Thanks again for all your support...

So I think that you are saying that the if the client was there all of December, say arrived in November and has not left yet, then December has 31 days so the answer should be 31.  But the formula is giving 30, I suspect.  So I think that we need to add 1 unless the client discharges during the period.

So we will not break the existing formula, just build on it.  That so that is nice thing about using formula variable.


The very last line just says 

$DaysStoredPriorMonth

ie, it says to just put put out that value previously calculated.

So let's change that to be

IF(
IsNull($ShipDate), $DaysStoredPriorMonth + Days(1), // still //here, so count Last Day of the month
$ShipDate > $LastDayofPriorMonth, 
$DaysStoredPriorMonth + Days(1), //left after EOM, so //count last day

$DaysStoredPriorMonth) // else left during month so don't //charge last day

Does your brain just work this way all the time-do you write "code" for a living??? Its like speaking another language-
I do get this syntax error:
The expression $DaysStoredPriorMonth on the left hand side of the operator "+" is of type number while the expression Days(1) on the right hand side is of type duration. The operator "+" cannot be used with these types of expressions.

I read up on days and duration not cooperating-but not sure what would be changed to make this work? Can I flip so it reads "Days(1) + $DaysStoredPriorMonth" or does that do more harm? I tried a adding a plus sign to the "Days(+1)....didn't work...

Sorry, I forgot that we are already a numeric field.

so we just need +1 and not + Days(1)

IF(
IsNull($ShipDate), $DaysStoredPriorMonth + 1, // still //here, so count Last Day of the month
$ShipDate > $LastDayofPriorMonth, 
$DaysStoredPriorMonth + 1, //left after EOM, so //count last day 

$DaysStoredPriorMonth) // else left during month so don't //charge last day

As for our other question,

"Does your brain just work this way all the time-do you write "code" for a living??? "

The answer is yes and yes.

I used to have a real job with 100 people reporting to me and meetings and budgets up until 11 years ago.  Then we sold the family business and I needed Plan B.  So now I solve puzzles using Quick Base for my clients, ie I write apps and help them when they get stuck.  So, it's not really work, it is more like paid puzzle solving.

Haha...so I was overthinking the solution-I have learned alot! 
Thanks for answering the question: I know you helped me once before-I was wondering how you manage to do what you do...As a not for profit we have a small IT team, but we are on our own for these things...trying to do self learning while also doing our "job". 
I work with people with dementia-so I will say that doing this kind of brain work is so good for your head!!!! It will serve you well in as you "age"!!!
Once again thank you for taking the time to support and help...
Cheers and enjoy your upcoming weekend-hope the weather is good for you!!!

OK well I have a soft spot for non profits, so if you really get stuck and I don't get to you on the forum, you can contact me directly for small stuff too.  But I will watch for you on the forum.

Dementia is tough.  I'm seeing it first hand with my father and friends parents.  The progressiveness of the disease seems to be linear, and it it just a question of the slope of the line as to how different people are affected. But the progression down that slope seems relentless.

Awww thanks-I really appreciate that-but know I will do my best to learn and try first..and only then will I call uncle...
That being said-I feel for you, watching a family member change is a roller coaster ride of good and bad...Just saying-from anywhere in the world if you ever want some "dementia" support/counsel reach out and we can communicate outside of the forum- sometimes you just need to brain storm dementia communication like I do formula stuff!!!
Thanks again...