How to convert dates by working backwards from end of month?

0

I'd like to convert dates to weeks of month, but I'd like the weeks to be counted back to front, kind of. So if there are 5 weeks in a month, I'd like the last 7 days to be week 5, last 8 to 14 days to be week 4 and so on. The first week may or may not have 7 days.

Would love to hear your advice.

sonic99

Posted 2019-07-22T11:36:43.517

Reputation: 51

(day_in_month - current_day) DIV 7. – Akina – 2019-07-22T12:00:12.727

Answers

1

With a date in A1, in another cell enter:

=ROUNDDOWN((DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/7,0)+1

enter image description here

EDIT#1:

To reverse the order, use:

=4-ROUNDDOWN((DATE(YEAR(A95),MONTH(A95)+1,0)-A95)/7,0)+1

instead.

Gary's Student

Posted 2019-07-22T11:36:43.517

Reputation: 15 540

Thanks so much for your help. The only thing is, I’d like your week 1 to be week 5, and week 5 to be week one. How would I go about reversing it like that? – sonic99 – 2019-07-22T14:00:10.940

@sonic99 See my EDIT#1: – Gary's Student – 2019-07-22T14:52:16.280

Asked: 2019-07-22T11:36:43.517

Viewed: 28 times

Active: 2019-07-22T14:51:50.387