Setting current week as week 0 on weekly cashflow

[AndrewRichards]

Hi,

Can someone please help me? My excel skills are basic at best, as are my explaining skills so please bear with me lool.

We've got a daily cashflow that feeds into our weekly cashflow. I'd like (actually our CFO would) our weekly cashflow to show the current week as week 0, with the week before being week -1 and week after being week 1. I'm currently doing this manually and it's a pain.

Anyone have a work around? I've attached a copy of the headings. It's row 2 that i'd like to auto update so that each new week, the week we are in becomes week 0 and everything moves along.

I could also do with some help on a different matter but it's not a priority for me. In row 3 we have the week number. This uses the following formulae "=WEEKNUM(F4,21)" to take the week ending date from below and give a week number. So 30-Apr-21 in F4 becomes 17 in F3.

My issue is that once it runs for more than a year there will be two 17's which will effect the criteria for the following formulae used to group the daily days into weekly. We use the following formulae to group the daily data =SUM(IF('GBP Cashbook'!$B$1:$B$196=Summary!$A16,IF('GBP Cashbook'!$C$4:$ZW$4=Summary!AG$5,'GBP Cashbook'!$C$1:$ZW$196))). The second "IF" that refers to AG5 is looking at the week number, which in the case of above would be 17.

If we extend this cashflow to 2022, there will be two 17's so it will bring back data from both and make the summary wrong.

Is there a way I can change the original formulae (=WEEKNUM(F4,21)) so that it makes the week number display as 17-21, so the week number it is with the year inserted?

Help on either matter would be greatly appreciated!

Cheers

[AliGW]

Try this:

=WEEKNUM(F4,21)&"-"&RIGHT(YEAR(F4),2)

[AndrewRichards]

That's worked for the second part - thanks Ali!

[samba_ravi]

try below formula in B2, Copy and paste towards right
="Week "&IFERROR(1/(1/COUNTIFS($B4:B4,">"&TODAY())),-COUNTIFS(B4:$BA4,"<="&TODAY())+1)

Please Login or Register  to view this content.

[AliGW]

You need to expand on what exactly you mean by this:

the week we are in becomes week 0 and everything moves along.

[AndrewRichards]

try below formula in B2, Copy and paste towards right
="Week "&IFERROR(1/(1/COUNTIFS($B4:B4,">"&TODAY())),-COUNTIFS(B4:$BA4,"<="&TODAY())+1)

Please Login or Register  to view this content.

This has done it! Thanks Samba!

If you find a chance might you be able to explain what exactly each part of the formulae is looking at so i can try and learn myself?

No worries if not, just having it correct is good enough for me.

[AndrewRichards]

You need to expand on what exactly you mean by this:

Sorry, I meant the week ending 19-11-2021 would be considered as week 0. Then last week, ending 12-11-2021 would be week -1 and next week would be week 1.

Samba has solved it above though so please don't use any time on it.

[AliGW]

I won't, but if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.