# Trying to figure total unearned amount billed 45 days in advance...

#### HeatherK

##### New Member
HELP! Excel beginner....exported spreadsheet from Access to Excel. I have a column with a renewal date for each customer (billing based on the renewal date, bills 45 days in advance), a column with the amount billed, and a column with frequency of billing (1-annually billed, 4-quarterly, 12-monthly)
EXAMPLE:
CUSTOMER RENEWAL DATE AMOUNT FREQUENCY
abc company 7/15/07 \$1,950.00 4

so, they are billed 6/1, 9/1, 12/1, and 3/1 (as of 6/1 billing, the entire amount is "unearned")

Need a spreadsheet to calculate how much is unearned at any given time.

Any suggestions? Does this even make sense to anyone? Any help is MUCH appreciated.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### DonkeyOte

##### MrExcel MVP
could you provide some more sample data with projected results... ie can billing dates be weekends or must you calculate 45 days back to nearest working day etc ? Or was your ex. just convenient -- would you always bill say on 1st or 15th of the month etc...

Re: revenue recognition -- can you elaborate ... are you saying unearned revenue is simply sum(billings) where billing date < renewal date or is it more complex ? RevRec normally is...

If you could come back with more data I'm sure we could help out - with one example it's difficult -- if you could give say ex of annual & monthly scenarios with different renewal dates that would help us provide a possible solution.

Replies
2
Views
314
Replies
5
Views
263
Replies
3
Views
382
Replies
3
Views
310
Replies
9
Views
538

1,191,032
Messages
5,984,248
Members
439,879
Latest member
KingGoulash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back