0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Tibneo
  • 39
  • 1
  • 9

3 Answers3

2

UNTESTED. Please try: 

=IF(DAY(TODAY())<25,DATE(YEAR(TODAY()),MONTH(TODAY()),24),DATE(YEAR(TODAY()),MONTH(TODAY()+1),24))
pnuts
  • 58,317
  • 11
  • 87
  • 139
1

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

enter image description here

EEM
  • 6,601
  • 2
  • 18
  • 33
0

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: enter image description here

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

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • exactly, i used the TODAY() formula to do it with the IF() formula as well, bur it wasn't working like i wanted it to. now i'm going to try to understand what you did here to make it work so well. – Tibneo Oct 19 '15 at 17:00
  • @Tibneo - I've edited my post to include an explanation - let me know if I can explain anything further! – BruceWayne Oct 19 '15 at 17:16
  • I accidentally commented on the wrong post, but I ended up noticing where I went wrong in the editing process. I have two credit cards, one visa, one mastercard, both obviously with different pay dates, and i was curious as to how i could edit the formula to reflect the new payment date, and for some reason when i was doing it, i didnt edit all three instances of 25 or 24, but I figured it out and have nothing but gratitude for your help! thanks again! – Tibneo Oct 19 '15 at 17:55
  • and yes, that helps perfectly! you're simply replacing today's day date with the due date, so replacing 19 with 24, unless today is greater than 24, in that case, you still replace it, but you add one to the month digit. i figured it out! not hard at all! thank you so much! – Tibneo Oct 19 '15 at 18:02
  • @Tibneo - I did one last edit, adding a table to hold different cards' due dates, which is more robust. Let me know if you have any questions! – BruceWayne Oct 19 '15 at 18:14