Display the upcoming due date

问题:

I am working with Microsoft Excel for Mac 2011 doing some personal finance and trying to devise a formula to display a specific date.

I have a credit card bill that is due on the 24th of every month. I have the name in Column A, and the Date it is due in Column B. Say that the current month is October, and the bill will be due on the 24th, I want it to display 10/24/15 (mm/dd/yy). I do not want to show any previous dates or current date, I only want to display the upcoming due date, and I want it to remain set on 10/24/15 until 10/25/15 where it will show me the next due date as 11/24/15, the very next month.

I need it to show the due date from 09/25/15 until 10/24/15. Then on 10/25/15 I need it to display the next due date.

回答1:

I think I understand. For my example, my data is set up like this:

     A         B
1   Name    Due Date
2   Visa    10/24/2015

For Cell B2, I have this formula: =IF(DAY(TODAY())>=25,DATE(YEAR(TODAY()),MONTH(TODAY())+1,24),DATE(YEAR(TODAY()),MONTH(TODAY()),24))

I am assuming that you'll be opening the spreadsheet and want the month to update to the one we're currently in (hence using Today()).

Edit: To break it down -

Using =today() will return today's date in default format (pretty sure it's whatever your default format is, i.e. mm/dd/yyyy). So, using =Month(today()) will just return the month of today's date...just as Year(today()), day(today()) will return "today's year" and day, if that makes sense.

The If statement looks to see if today's numerical date is greater than or equal to 25. If it is, then return the date with today's year, today's month plus one, and the 24th. If today is less than the 25th, then return today's year, today's month, and 24 for the day.

Hopefully that helps!

edit2 - A more robust formula, allowing you to keep a separate table with the date in each month that your bills are due. Essentially, you're just replacing the "magic number" 24 with a Vlookup formula, VLOOKUP(B3,$F$2:$G$4,2,FALSE).

Here's how it works:

Instead of 'hard coding' the 24 in the formula, which you have to change every time your card changes (or you have a different due date each month), you can create a table to have these values. My range F2:G4 shows you which card is due which date. (I.e. the VISA is due on the 24th, so after the 24th, show next month). This way, you drag that formula down your "B" column, and it updates itself automatically. (See how AmEx I say is due on the 10th. But today is the 19th, so thus, we get November 10th as the due date.)

So for copying purposes, the new formula is =IF(DAY(TODAY())>VLOOKUP(B3,$F$2:$G$4,2,FALSE),DATE(YEAR(TODAY()),MONTH(TODAY())+1,VLOOKUP(B3,$F$2:$G$4,2,FALSE)),DATE(YEAR(TODAY()),MONTH(TODAY()),VLOOKUP(B3,$F$2:$G$4,2,FALSE))). (Of course, you'll need to change the ranges as necessary for your sheet).

回答2:

UNTESTED. Please try:

=IF(DAY(TODAY())<25,DATE(YEAR(TODAY()),MONTH(TODAY()),24),DATE(YEAR(TODAY()),MONTH(TODAY()+1),24))

回答3:

This solution assumes the following:

• The Microsoft Excel for Mac 2011 includes the Excel function EOMONTH
• The Accounts Payable Table is located at B1:D6 (adjust as required) with the following fields (see Fig. 1):

Account : Name of the payable account

Due Day : Day of the month when payment is due

Due Date : Next Payment date

Enter any of these two formulas in C2 and copy till last record

Formula 1:

=IF(DAY(TODAY())>$C3,
EOMONTH(TODAY(),0)+$C3,
EOMONTH(TODAY(),-1)+$C3)

Formula 2:

=EOMONTH(TODAY(),
IF(DAY(TODAY())>$C3,0,-1))
+$C3