如何获得四分之一的约会?
在 Excel 中,没有直接函数可以从日期中获取季度。但您可以为此创建一个公式。
是的,您可以使用公式从日期计算季度。即使您可以使用不止一种方法。好吧,如您所知,一年总共有四个季度,但事实是您可以通过不同的方式从日期中获取季度。
第一个假设是按正常日历计算的一年,第二个假设是按纳税日历计算的一年。在今天的帖子中,我将向您展示使用两种日历获取季度编号的方法,并且我还将与您分享不止一种方法。
那么,让我们开始吧。
1. 使用 ROUNDUP 和 MONTH 函数获取季度
使用 ROUNDUP 和 MONTH 的组合是查找日期的季度的最佳方法。它以数字形式返回四分之一(如 1、2、3、4)。
这是公式。
=ROUNDUP(MONTH(A1)/3,0)
这里我们使用 2018 年 5 月 26 日作为日期,公式在结果中返回 2。如果您想在季度数字前添加 Q,可以使用下面的公式。
="Q-"&ROUNDUP(MONTH(A1)/3,0)
这个公式是如何运作的?
这个公式分为三个部分。
在第一部分中,我们使用了 MONTH 函数并引用了返回月份数的日期。
之后,在第二部分中,我们将这个月的数字除以 3,得到一个带小数位的数字。
最后,在第三部分中,我们使用 ROUNDUP 函数进行四舍五入(将数字保留到小数位),从而得到实际的四分之一。
这是真实的事情:你知道有 4 个季度和 12 个月。正确的?如果将所有月份数字除以三(是的,只是除以 3),您将得到如下所示的结果。
当您将它们四舍五入时,您总是会得到该月的季度数。
2. 使用会计日历获取季度
在上面的示例中,我们使用了一个简单的公式来计算日期中的季度,但有时您需要使用会计日历。
对于会计日历季度计算,我们需要使用不同的公式,在该公式中,我们需要组合 SELECT 和 MONTH 函数。
公式如下:
=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
(这里我使用的是印度财政年度,从4月1日开始到3月31日结束)
它在结果中返回 1,因为月份是 5 月,而我们的年份从 4 月开始,而 5 月是一年中的第二个月,属于第一季度。
这个公式是如何运作的?
在这里,我们组合了两个不同的功能,它分为两部分。
在第一部分中,我们使用 MONTH 函数从日期中获取月份数,这里返回 5。
之后,在第二部分中,我们使用选择函数来根据 MONTH 函数返回的月份数来获取数字。
您可以从这里了解有关选择的更多信息。
如果你看一下,在 CHOOSE 函数中,我们指定了 12 个不同的数字 (4,4,4,1,1,1,2,2,2,3,3,3),所以当 MONTH 返回一个数字时,CHOOSE 返回一个数字的列表。
这是交易:
对于前三位数字,我们有 4,这意味着如果它返回 1、2 或 3(1 月至 3 月),我们将得到 4,接下来的三个月我们将得到 1(这是因为我们的年度税从 4 月开始)。
3.使用VBA代码将日期转换为季度
这里我们使用 VBA 代码将日期简单地转换为其季度数。这是代码:
Sub convert2Quarter() Dim Rng As Range For Each Rng In Selection If IsDate(Rng.Value) = True Then Rng.Value = DatePart("q", Rng.Value) Rng.NumberFormat = “General” End If Next Rng End Sub
要使用此代码,只需将其添加到您的 VB 编辑器中即可。
然后只需选择需要将日期转换为季度数的单元格,最后运行此代码。
以下是这段代码的工作原理…
当您运行此代码时,它首先检查单元格是否有日期,如果是日期,则将其转换为季度数。
我们在这里使用了两个不同的 VBA 函数:
- ISDATE:检查单元格是否有日期。
- DATEPART:将日期转换为季度。
检查一下:对于 VBA 新手来说最有用的 100 个宏代码 | Excel 中的 VBA 是什么
4. 从日期获取季度开始日期
让我们忘记季度编号并使用季度开始日期。
这意味着,如果我们的日期是 2018 年 5 月 26 日,而不是季度编号 2,我们可以使用 2018 年 4 月 1 日,这是该季度第一个月的开始日期。这是我们需要使用的公式:
=DATE(YEAR(A1),FLOOR.MATH(MONTH (A1)-1,3)+1,1)
当您输入此公式时,它仅返回季度的开始日期。
这个公式是如何运作的?
该公式由四个不同的部分组成,为您提供学期的开始日期。
在第一部分中,我们使用了 MONTH 函数,它返回日期的月份数。
因此,当 MONTH 返回 5 作为月份数字(然后减去其中的一个)时,在第二部分中,FLOOR 将其四舍五入为 3,然后加一,这样我们就可以得到 4,即同季度的第一个月(四月)?
之后,在第三部分中,我们使用 YEAR 函数从实际日期获取年份。
在第四部分中,我们简单地组合了 DATE 函数来创建一个组合了前两部分中的值的日期。
5. 从日期获取季度结束日期
好的,所以您需要季度的结束日期而不是开始日期或数字,为此我们可以使用的公式是:
=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1
对于 2018 年 5 月 26 日,它会在 2018 年 6 月 30 日返回,这是该季度的最后一个日期。
这个公式是如何运作的?
在这个公式中,我们有四个不同的部分。
在第一部分中,我们组合了 INT 和 MONTH 函数。 MONTH 返回日期的月份数,然后将其除以 3。之后,INT 将其四舍五入为最接近的整数。
最后,我们将其乘以 3 得到 6(六月),即该季度的最后一个月,然后加 1。
简单来说,这部分公式只是将实际日期的月份转换为下一季度的第一个月。
在第二部分中,我们使用 YEAR 函数从实际日期获取年份。
之后,在第三部分中,只需用DATE函数用1来完成日期即可。
对不起!还有一件事。
在第四个中,整个公式减 1,因此我们可以得到该季度的最后一个日期,而不是下一个季度的第一个日期。
获取 Excel 文件
结论
通过简单地组合函数,您可以创建一个简单的公式来计算日期的季度。在本文中,我们为此探索了四个公式 + VBA 代码。
坦率地说,#1 是查找四分之一日期最常用且最简单的方法,但如果您愿意,您可以使用其余的方法。我希望这篇文章对您有所帮助,但现在请告诉我这一点。
您知道另一种从日期计算季度数的方法吗?
请在评论部分与我分享,我很乐意听取您的意见,并且不要忘记与您的朋友分享此技巧。我相信他们会很感激的。