How to calculate percent through the week / month in google sheets?-CodePudding

I want to calculate how far I am through the week and / or the current month in Google Sheets. What's the best way to do this?

Ideally in a single cell too. I also want to account for the difference in number of days from month to month.

CodePudding user response：

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

CodePudding user response：

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)