如何在Google表中计算到一周 /月的百分比？

发布于 2025-01-20 22:38:49 字数 80 浏览 0 评论 0原文

我想计算我在 Google 表格中一周和/或当月的进度。最好的方法是什么？

理想情况下也是在单个细胞中。我还想考虑每个月的天数差异。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

听，心雨的声音 2025-01-27 22:38:49

请参阅：

月进度：

=1-(EOMONTH(TODAY(), )+"23:59:59.999"-NOW())/
 (EOMONTH(TODAY(), )+"23:59:59.999"-(EOMONTH(TODAY(), -1)+1))

周进度：

=1-((TODAY()+7-WEEKDAY(NOW(), 2)+"23:59:59.999")-NOW())/
 ((TODAY()+7-WEEKDAY(NOW(), 2)+"23:59:59.999")-(TODAY()-WEEKDAY(NOW(), 2)))

see:

month progress:

=1-(EOMONTH(TODAY(), )+"23:59:59.999"-NOW())/
 (EOMONTH(TODAY(), )+"23:59:59.999"-(EOMONTH(TODAY(), -1)+1))

week progress:

=1-((TODAY()+7-WEEKDAY(NOW(), 2)+"23:59:59.999")-NOW())/
 ((TODAY()+7-WEEKDAY(NOW(), 2)+"23:59:59.999")-(TODAY()-WEEKDAY(NOW(), 2)))

demo sheet

回复收藏 0 原文

吻安 2025-01-27 22:38:49

对于每周，我通过在本周开始时使用当前的时间戳（now（）（）（）（））和时间戳，然后将两者都转换为Unix Epoch时报。然后，我使用了它们之间的差异，并将一周内的秒数除以秒数（518400），这给了我百分比的小数now now（） is整个星期。

=((now()-DATE(1970,1,1))*86400-((to_date(TODAY()-weekday(now(),3))
 + timevalue("00:00am"))-DATE(1970,1,1))*86400)/518400

对于每月，我进行的类似于每周的方法类似，但是，为了说明不同月份的不同天数，我计算了本月末的时间戳，然后在UNIX时期计算了时间戳，然后在月初减去了时间从此开始，本月有多少秒钟。

=((now()-DATE(1970,1,1))*86400-((to_date(eomonth(today(), -1)+1)
 + timevalue("00:00am")-DATE(1970,1,1))*86400))/((to_date(eomonth(today(), 0))
 + timevalue("23:59")-DATE(1970,1,1))*86400-(to_date(eomonth(today(), -1)+1)
 + timevalue("00:00am")-DATE(1970,1,1))*86400)

For weekly, I did this by taking the current timestamp (now()) and the timestamp at the beginning of the week and converting both to unix epoch times. Then, I used the difference between them and divided both by the number of seconds in a week (518400) and this gave me a decimal for the percent the now() is through the week.

=((now()-DATE(1970,1,1))*86400-((to_date(TODAY()-weekday(now(),3))
 + timevalue("00:00am"))-DATE(1970,1,1))*86400)/518400

For monthly, I did this similarly to the weekly method but, to account for the differing number of days in different months, I calculated the timestamp at the end of the month in unix epoch time and then subtracted the time at the beginning of the month from this to work out how many seconds are in the current month.

=((now()-DATE(1970,1,1))*86400-((to_date(eomonth(today(), -1)+1)
 + timevalue("00:00am")-DATE(1970,1,1))*86400))/((to_date(eomonth(today(), 0))
 + timevalue("23:59")-DATE(1970,1,1))*86400-(to_date(eomonth(today(), -1)+1)
 + timevalue("00:00am")-DATE(1970,1,1))*86400)

回复收藏 0 原文

~没有更多了~