Formula to calculate total amount due in previous month

syolleck

Hi all,

I'm trying to create a formula that will achieve the following:

• Sum up the total # of days that fall between two dates within the last previous month
• Date 1: Admit date
• Date 2: Discharge date

For example, Patient A was admitted on 6/1/24, and discharged on 7/15/24. I'd like to know how many of those patient days fell into the previous month (Today's month - 1, so July).

I also have a field calculating the total # of days between those dates, but not sure that's helpful.

Another example:

Patient B was admitted on 2/14/24 and discharged on 8/5/24. How many days did the patient spend in the hospital last month?

This is for invoicing purposes, so we're trying to assume that invoices for each month are sent out after the month in which they were incurred.

Appreciate your assistance- don't even know where to start here!

Paul Newcome

Try something like this:

=IF([Discharge Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), IF([Discharge Date]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), DAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), DAY([Discharge Date]@row))) - IF([Admit Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONT(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), DAY([Admit Date]@row), 0)

syolleck

Hi Paul- thank you for your assistance. I'm still struggling with this. See below for the formula i used with the actual column names. When I click into the formula, it doesn't seem to be registering the column names. Any suggestions?

=IF([Respite Discharge Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), IF([Respite Discharge Date]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), DAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), DAY([Respite Discharge Date]@row))) - IF([Respite Arrival Date]@row >=IFERROR(DATE(YEAR(TODAY()), MONT(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), DAY([Respite Arrival Date]@row), 0)

heyjay

Typo here,

Paul Newcome

Sorry about that. Looks like my fingers and brain weren't on the same wavelength.

=IF([Discharge Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), IF([Discharge Date]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), DAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), DAY([Discharge Date]@row))) - IF([Admit Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), DAY([Admit Date]@row), 0)

syolleck

Thanks, Paul. It's still not working, unfortunately. Both the Respite Discharge & Arrival Date columns are formatted as dates. Appreciate your assistance!

=IF([Respite Discharge Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), IF([Respite Discharge Date]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), DAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), DAY([Respite Discharge Date]@row))) - IF([Respite Arrival Date]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), DAY([Respite Arrival Date]@row), 0)

Paul Newcome

Can you provide screenshots of the column names open as if you are about to edit them?

syolleck

See below. The formula is coming up as #unparseable. Thank you!

heyjay

What are your expected answers for your provided dates below?

Paul Newcome

It is coming up just fine for me. To confirm… Are you putting this formula on the same sheet as the two columns being referenced?

The screenshot below is a copy/paste of the formula from your last post that had the formula typed out:

syolleck

Hi all- the formula worked when I pasted it in a cell vs. in a summary field. I'll create a new column for previous month's patient days and total it using a summary field. Thank you so much for your help!!

syolleck

Hi all- the formula worked when I pasted it in a cell vs. in a summary field. I'll create a new column for previous month's patient days and total it using a summary field. Thank you so much for your help!!

In my data, I'm seeing that a patient with an 8/8 respite arrival date and an 8/8 respite discharge date has 23 days in the previous month (July). I'm also seeing that a patient with a respite arrival date of 8/6/24 who hasn't yet been discharged has -6 patient days in the previous month.

Paul Newcome

Yes. The formulas provided were for column formulas and not sheet summary fields.

Are you able to provide screenshots of the two rows in question that are outputting the incorrect numbers of days?

syolleck

Please see below for a few examples. The row in gray correctly calculated the # of days, but the date range fell exclusively within the previous month. Thank you!