# Formula to calculate total amount due in previous month

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 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!

• ✭✭✭✭✭✭

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)

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

• ✭✭✭✭✭

Typo here,

...

• ✭✭✭✭✭✭

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)

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

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭

...

• ✭✭✭✭✭✭
edited 08/08/24

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:

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

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

• ✭✭✭✭✭✭

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?

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!