00:00how would you like to explore further details on expanding the recently
00:03published dynamic calendar template one of my subscribers wanted to include
00:06week number on his calendar like this well what are we waiting for let's dive
00:10in the first thing I want to do is to introduce a brand new column just on
00:14column A itself like this and this column will be dedicated to display the
00:19week number I'm just gonna make it a bit more customized here and I'm gonna call
00:23it week number and bold and maybe do a border around it like this and change
00:29the background color on that cell so that it stands out a bit I'm gonna color
00:34something like this once that's done now we're gonna determine the week number
00:39for it the way to do it is to basically build a date value using a date value
00:44function well let me show you what I mean by that gonna say equal b5 which is
00:50gonna give you 11 and concatenate that with a space followed by the month which
00:56is easily available here and then concatenate and put a space and then
01:00concatenate with a year like this and this will essentially give you a date
01:07itself this 11 May 2015 like this now we're gonna convert this into a date
01:13format the way to do it would be to use a function called date value like this so
01:18if you encapsulate that text string or the date in its string with a date value
01:24like this it will give you number of days since January 1st 1900 now this is in
01:30date format here now to determine what is the week number for that particular day
01:36you're gonna have to encapsulate the date value under a function called week num
01:42like that and that will give you the week number for 11th of May 2025 now the
01:50rest of the cell above and below is quite simple all you do have to do is say
01:53equal a 5 plus 1 will give you 21 and on that row 7 you're gonna say a 6 plus
02:031 and
02:04row 8 we're gonna simply say a 7 plus 1 and then for cell a 4 we're gonna say
02:10equal to a 5 minus 1 and then on row 3 we're gonna say equal to a 4 minus 1
02:19and
02:19you can see that it creates a week number for it nicely there for you now if you
02:23were to go ahead and make those changes you can see that the week number changes
02:28accordingly let's do a test on January make sure that it goes to 1 on that very
02:32first week and even if you change the year and if you jump to a different month it
02:39is working job done completed
Comments