00:00how do you get random date that excludes weekend and statutory holiday that was one of the questions
00:05from my viewer this is how you can do it i'm gonna repurpose the previous formula that was
00:10featured on my previous video i'll leave the link to that video on the description here so you're
00:14gonna go to this cell equal ran between and paste it if you get this big number this means number
00:20of days since january 1st 1900 now to convert this into nice year month and day format you're
00:26gonna press ctrl one number tap date and okay and you can see it's producing random date right now
00:32so now this date could possibly include weekends as well to exclude weekend you're gonna encapsulate
00:38this ran between with workday function like this and the first argument would be the ran between
00:44date ran between date date and the second argument will be one to indicate one day ahead close
00:51parenthesis and hit enter and now i can apply to the rest of the row like this now just to validate
00:57and make sure that there's no weekends on these days you're gonna let's use a function called text
01:03i'm gonna use this date as a first argument comma and we just want to see the date so if you put four
01:08d's it'll give you a fully spelled day of the week if you put three d's you will abbreviate it so i'm
01:15gonna put four d's and if if you apply to the rest of the row and press f9 to recalculate you can see
01:20not these days are weekends so a lot of people ask me why do you put one instead of zero in excel
01:28january 1st 1900 is considered as a sunday that's why if you put zero here you can see you there's a
01:34possibility that you will get weekend if you put f9 you can see weekends are also included so i'm gonna put
01:41changes to one and apply the same formula to the rest of row you can see none of the days are weekends
01:50now how do you generate a random date that excludes weekends and stat holiday i got a list of stat
01:56holiday here and i want to exclude this and the weekend so basically what you do is you copy the
02:01formula you paste it in here and what you do is that on this formula the third argument will be the
02:07column for the stat holiday like this select all the stat holiday and press f4 because i'm gonna apply
02:14the same formula to the rest of the rows and hit enter and then you can apply the same formula to
02:19the rest row like this and it will generate days without weekends and stat holiday and just to check
02:25we can do the same formula here text this one here convert dddd and apply the same formula you can see
02:37it doesn't include any weekend and to validate and make sure that none of these days actually coincide
02:44with these days we can do a quick and dirty conditional formatting here
02:49and you should see when you do prep f9 none of this colored line matches over here
03:05you
