是否根据条件使用Google表格中的公式来确定即将到来的日期? [英] Is there a formula for upcoming date using formula in Google sheet based on condition?
问题描述
我有一个Google工作表,我需要根据A列中设置的开始日期获取下一个即将到来的日期。
任何指针都会受到极大的赞赏吗?我无法展示所做的努力,因为我完全不熟悉使用Google表格进行此类重复操作
此行为(下一个从今天开始直到今天为止的日期)可以通过以下公式手动实现:
= {
从今天起的下一个日期;
ARRAYFORMULA(
IFS(
A2:A> = TODAY(),
A2:A,
B2:B = Daily,
TODAY()+ MOD(TODAY()-A2:A,C2:C),
B2:B = Weekly,
TODAY()+ MOD(TODAY()-A2:A,7 * C2:C),
B2:B =每月,
EDATE(A2:A,ROUNDUP((12 *(YEAR(TODAY())-YEAR(A2:A)))+( MONTH(TODAY())-MONTH(A2:A))-IF(DAY(TODAY())< DAY(A2:A),1,0))/ C2:C,0)* C2:C),
正确,
)
)
}
对于其他选项(例如每月的第二个星期一及其他),应在 IFS
部分中实现其他选项。
如果您对一个小情况感兴趣,那么下一个日期是需要开始日期(屏幕截图上的列 F:F
) ed,那么公式就会简单得多:
= {
下一个日期;
ARRAYFORMULA(
IFS(
B2:B =每日,
A2:A + C2:C,
B2:B =每周,
A2:A + 7 * C2:C,
B2:B =每月,
EDATE(A2:A,C2:C),
True,
)
)
}
再次提供其他选项您需要将相应的部分添加到 IFS
。
I have a google sheet where I need to get the next upcoming date based on the start date set in column A
Any pointers are greatly appreciated? I am unable exhibit the efforts as I am completely new to this sort of recurrence using Google sheets
https://docs.google.com/spreadsheets/d/1g_UNg4MjDy3gFufpjZtMRkbBz80K3scQdZOaiAnZi7I/edit#gid=0
This behavior (the next date from today including today) could be implemented manually by this formula:
={
"Next date from today";
ARRAYFORMULA(
IFS(
A2:A >= TODAY(),
A2:A,
B2:B = "Daily",
TODAY() + MOD(TODAY() - A2:A, C2:C),
B2:B = "Weekly",
TODAY() + MOD(TODAY() - A2:A, 7 * C2:C),
B2:B = "Monthly",
EDATE(A2:A, ROUNDUP((12 * (YEAR(TODAY()) - YEAR(A2:A)) + (MONTH(TODAY()) - MONTH(A2:A)) - IF(DAY(TODAY()) < DAY(A2:A), 1, 0)) / C2:C, 0) * C2:C),
True,
""
)
)
}
For additional options (like "every 2nd monday of the month" and others) additional options should be implemented in that IFS
part.
If you are interested in a trivial case where the next date from the start date (column F:F
on the screenshot) is needed, then the formula would be much simpler:
={
"Next date";
ARRAYFORMULA(
IFS(
B2:B = "Daily",
A2:A + C2:C,
B2:B = "Weekly",
A2:A + 7 * C2:C,
B2:B = "Monthly",
EDATE(A2:A, C2:C),
True,
""
)
)
}
Again, for additional options you'll need to add corresponding part to the IFS
.
这篇关于是否根据条件使用Google表格中的公式来确定即将到来的日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!