Formula for counting down to a due date

**Adejumoke** · 08-20-2013, 07:12 AM

i have a date that i supplied goods to a customer e.g 17/7/2013 in cell A1 (date supplied) and the total amount of the goods supplied in cell B1, which the customer has 30 days for the payment to expire, so i need a formula that will countdown each day(i.e from 30, 29, 27 ---------0 and it will bring out the amount as well (i.e 4 days remaining to paid up the value which is 2000). also i have a table in excel which goes like this: Name of Customer/ Amount Paid/ payment due date /0-5days/6-20days/21-40day. so when the decrease fall on 6-20 days, the payment reminder should move to 6-20 day. When it reaches the deadline day, which is 30 days the reminder should move to payment due.

Note: the countdown should always be automatic update without me opening my system always.
Here is my attached format in excel sheet.

**ramananhrm** · 08-20-2013, 07:36 AM

Try this file.

I have used the conditional formatting to hide the inappropriate values.

But the formula will be available in all the cells.

**L-Drr** · 08-20-2013, 07:37 AM

I have filled your three deadline categories for the first row. Is this what you meant?
Could you explain more clearly what you want in the Payment Over Due column?

**Adejumoke** · 08-20-2013, 07:52 AM

L-Drr, thank, But this what i mean
when the deadline figure range fall into 1- to 5, i want it to move to 1-5, when the deadline figure range fall into 6- to 20, i want to move to 20, but when 31 day i want it to move to payment overdue.

**L-Drr** · 08-20-2013, 08:02 AM

I don't understand what you mean exactly. Please formulate what you want as follows:

What value do you want under Payment Over Due and when do you want that?
What value do you want under 0-5 days and when do you want that?
What value do you want under 6-20 days and when do you want that?
What value do you want under 21-40 days and when do you want that?

**Adejumoke** · 08-20-2013, 08:04 AM

L- Drr, i want the Payment Over Due column, to say, : your customer payment is over due

**Adejumoke** · 08-20-2013, 08:05 AM

on the 30th

**Adejumoke** · 08-20-2013, 08:10 AM

under Payment Over Due : i want that on 30th and the "your customer payment is over due"
under 0-5 days ("4 days remaining to paid up the value which is 2000")
What value do you want under 6-20 days and when do you want that? ("6 days remaining to paid up the value which is 2000")
What value do you want under 21-40 days and when do you want that? ("22 days remaining to paid up the value which is 2000")

**L-Drr** · 08-20-2013, 08:11 AM

Ok I've put that in there, too. Was the rest already working like you want it to?

BTW mind that, when the payment is over due, you can't see anymore how long it's over due. I could build that into your sentence, if you like. So e.g. "Your customer payment is over due for ... days"

Edit: didn't see post #8 yet

**duffry** · 08-20-2013, 08:12 AM

As posted in the 'other' thread, not sure if needed here:
http://www.excelforum.com/excel-form...-due-date.html
______________

The thread is already marked as [SOLVED] but hopefully this will solve it for you.
You can have a cell display the number of days between now and a specified date with a formula such as:

Please Login or Register  to view this content.

Which I put in cell K3

I'm not sure how you want to have the result be displayed on your sheet but I put the following into cells G3,H3,I3 & J3 respectively:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Which put a "Yes" in the appropriate column based on the first formula in cell K3.

You can replace the "K3" in these formulas with the original one so you don't need to have the extra column, but it may be useful to have this value displayed.

Typically I would have conditional formatting on the value to show what range it is in.

**L-Drr** · 08-20-2013, 08:16 AM

Updated the file with your comments in post #8

**Adejumoke** · 08-20-2013, 08:23 AM

L-Drr, Thank you very much, you make my day.It work perfectly the way i want

**L-Drr** · 08-20-2013, 08:30 AM

You're welcome!

If you need help with copying the formula to the other cells, let me know.
If not, please mark the thread as solved (above your first post) and consider adding reputation points (in the lower left corner of a post)

**Adejumoke** · 08-20-2013, 10:44 AM

L-drr i need your help again please, when the payment over due pop up to 30 days, i want the amount the customer paid should change to red

**L-Drr** · 08-20-2013, 10:51 AM

Select E3-E19 (or more; all the amount cells), go to Conditional Formatting and use this formula:
=LEN(G3)>0
Then set the formatting

**Adejumoke** · 08-20-2013, 11:06 AM

i am using Microsoft excel 2003,is not working there

**L-Drr** · 08-20-2013, 11:12 AM

I've put it in the attached file.
If there is anything in G, the value in E in the same row will turn red.

**Adejumoke** · 08-20-2013, 11:20 AM

Thanks a lost, i don't know how to thank you. you are the best

**L-Drr** · 08-20-2013, 11:24 AM

You're welcome! If I stop learning Excel and you keep learning it, someday you can return the favor and answer my question

Originally Posted by L-Drr

please mark the thread as solved (above your first post) and consider adding reputation points (in the lower left corner of a post)

**JBeaucaire** · 08-20-2013, 07:36 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Formula for counting down to a due date

LinkBack

Thread Tools

Rate This Thread

Display

Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Re: Formula for counting down to a due date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula for counting down to a due date

Counting formula that looks up date and adds months to that date to count customers

Formula for counting date & time range

excel formula counting date to date in 4 columns

Bookmarks

Bookmarks

Posting Permissions