显示即将到期的到期日 [英] Display the upcoming due date

问题描述

我正在使用Microsoft Excel for Mac 2011做一些个人财务，并试图设计一个公式来显示一个特定的日期。

我有一个信用卡账单，每月24日到期。我在A栏中有名字，B栏中的日期。说当月是十月，账单将在24日到期，我希望它显示 10/24 / 15 （mm / dd / yy）。我不想显示任何以前的日期或当前日期，我只想显示即将到期的到期日期，我希望它保持在10/24/15直到10/25/15，它会显示下一个到期日日期为 11/24/15 ，下个月。

我需要显示到期日期09/25/15至10/24/15。然后在10/25/15，我需要它显示下一个到期日。

解决方案

我想我明白了。对于我的例子，我的数据设置如下：

  AB 
 1名称到期日
 2签证10/24/2015 
  

对于单元格B2，我有这个公式：
= IF（DAY（TODAY（））> = 25，DATE（YEAR（TODAY（）），月（TODAY（））+ 1,24），DATE（YEAR（TODAY（）），月（ TODAY（）），24））

我假设您将打开电子表格，想要更新到我们目前所在的月份（因此使用 Today（））。

编辑：要将其分解下来 -

使用 = today（）将以默认格式返回今天的日期（很确定它是什么你的默认格式是mm / dd / yyyy）。所以，使用 = Month（today（））将只返回今天日期的月份，就像年（今天（））， day（today（））将返回今天和今天，如果这是有道理的。

如果语句查看今天的数字日期是否大于或等于25.如果是，则返回日期今天的今天，今天的一个月加一个和第24个。如果今天不到25日，那么今天的今天是今天的一个月，今天是24日。

希望有帮助！

edit2 - 一个更强大的公式，允许你保持一个单独的表与日期在您的帐单到期的每个月。基本上，你只是替换

而不是在公式中使用 24 而不是硬编码，您每次更改卡片时都需要更改（或者您有不同的到期日期）每个月的日期），您可以创建一个表以具有这些值。我的范围F2：G4显示哪个卡是到期哪个日期。 （即，VISA将于24日到期，24日后到下个月）。这样，您可以将该公式拖放到B列，并自动更新。 （看看AmEx我在十号到底是怎么说的，但是今天是十九号，所以我们在十一月份收到十日）。

所以为了复制目的，新公式是 = IF（DAY（TODAY（））> VLOOKUP（B3，$ F $ 2：$ G $ 4,2，FALSE），DATE（YEAR TODAY（）），月（TODAY（））+ 1，VLOOKUP（B3，$ F $ 2：$ G $ 4,2，FALSE）），DATE（YEAR（TODAY（）），月（TODAY（）），VLOOKUP（ B3，$ F $ 2：$ G $ 4,2，FALSE）））。 （当然，您需要根据需要更改范围）。

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.

解决方案

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

这篇关于显示即将到期的到期日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！