duplicate row based on calculated number of months

[Guest]

Hi, I have an Excel workbook based on an Access query. It shows billing
dates and paid dates with amounts by client along with other stuff. Some
bills get paid the same months and some can take up to 12 months to get paid.
I need to display, per month, all bills that have not yet been paid, no
matter when they were submitted. Anyone have any ideas on how I can do this?
For example, if a bill was submitted in January and not paid until June, it
would show up in Jan, Feb, March, Apr, May, and June. Looking forward to
help as the boss just told me he needs this today.

 

[Bernie Deitrick]

How do you indicate that the bill has not been paid? Is a field blank, or does it say "Not Paid"?
I'm sure that we can give you a formula that will allow you to filter your datatable. For example,
if your date submitted values are in column B, starting in row 3, with headers in row 2, and if your
paid dates are in column C (or left blank if unpaid), then type the month name as a string (e.g.,
May) into cell B1, then use this formula in a free column:

=IF(AND(B3<DATE(YEAR(NOW()),MONTH(DATEVALUE($B$1 & "1, " &
YEAR(NOW())))+1,0),OR(C3="",C3>DATE(YEAR(NOW()),MONTH(DATEVALUE($B$1 & "1, " &
YEAR(NOW())))+1,0))),"Outstanding","")

That formula will put Outstanding into rows where the bill existed but wasn't paid for that
particular month. Then apply a data filter and just show "Outstanding" in that column to get a
report....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi,

Thanks for getting back to me. Now the boss wants to show all bills each
month, those paid and those not paid. I know when a bill hasn't been paid
because the cell is blank. I'll try your formula revising the IF to show
paid if false.