Download "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario"
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FM7NabAjRXco%252Fmaxresdefault.webp&w=1139&q=75)
Please wait. We're preparing links for easy ad-free video watching and downloading.
Table of contents
Similar videos from our catalog
%3Aformat(webp)%2Fi.ytimg.com%252Fvi%252F1vMLdQfsPi0%252Fmaxresdefault.jpg&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi%252Fja-p0VpDJ5U%252Fmaxresdefault.jpg&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi%252FzpTTXtnBsI8%252Fmaxresdefault.jpg&w=1139&q=75)
Video tags
Subtitles
Russian
00:00:00
Hello, how are you my friends?00:00:02
Welcome back to my00:00:05
YouTube channel Excel made easy and in this00:00:07
new tutorial I want to share with you00:00:10
a small mini application that00:00:13
we developed completely from scratch00:00:15
that will practically emulate an agenda00:00:19
but in this case an00:00:22
automated and interactive agenda with00:00:25
calendar using Microsoft Excel00:00:28
I want you to see that on the right side00:00:30
I have an Excel workbook OR I have00:00:32
practically a view inside the00:00:34
Excel workbook that has a data source00:00:36
that has three columns date and time a00:00:39
matter that I need to do or need to00:00:41
remember and a status if it is pending or00:00:44
finished and on the left side I have00:00:46
a sheet that allows me to select a00:00:48
month and a year to see how many issues00:00:51
I have in that month and year ago and here it00:00:53
is giving me the agenda of the day that I00:00:56
choose in the calendar What happens that00:00:59
this calendar with a formula But if00:01:02
I come and click here for example on00:01:04
day 7 Notice that this changes depending00:01:07
on the day that I load in this cell00:01:10
I want to see how many issues or how many00:01:13
activities I have for example on the day 2000:01:15
then I come and select 20 I want to00:01:17
see, for example, how many issues I have on00:01:19
Wednesday but in October, for00:01:22
example, 28, sorry, September because00:01:25
it would be the previous month of October and it00:01:27
tells me that I have no data, that is, that day I00:01:29
did not register data in the source of data,00:01:31
for example, on the 8th it is telling me00:01:34
that I have three pending completions 4,00:01:36
look at a fully automated agenda with a calendar00:01:38
using a00:01:42
set of formulas and functions00:01:44
obviously aligned to generate those00:01:46
formulas that allow us to develop00:01:48
this type of Mini applications with Excel00:01:51
that make us life Much easier and they00:01:53
allow us to keep a00:01:56
record of our activities,00:01:57
our affairs day by day as we00:02:00
need I00:02:01
have not seen this on the internet I have not00:02:04
seen this here on YouTube I think I am00:02:07
the person the first person to do it It's going00:02:09
to cover it, obviously in the spirit of00:02:10
giving each other the flowers, I'll just say it if you00:02:12
see it. On another channel they copied it from00:02:14
this one. The date is October 21, 2022.00:02:18
Look, October 21, Friday, October 21,00:02:22
2022. 540 in the afternoon, I'm00:02:25
developing this. tutorial so that the00:02:27
people of the Channel who follow Excel00:02:29
Made Easy do not follow me, they follow the00:02:31
Excel Made Easy project because00:02:33
they want to learn Excel, they want to Learn00:02:36
to manage this spreadsheet like00:02:38
professionals, like true00:02:40
self-taught professionals who manage00:02:43
knowledge to transform the00:02:45
data in useful information I am making00:02:48
this calendar or this agenda with an00:02:51
automated calendar or an00:02:53
automated calendar with an agenda or with a small one00:02:55
or an automated agenda with a small00:02:57
calendar I have not seen it on YouTube So00:03:01
if you see it after this date00:03:02
most likely It's because00:03:05
this video has been copied. Thank you very much in advance00:03:07
for choosing to watch my videos. I truly00:03:10
sincerely shake your hand. I give you a00:03:12
big hug to all of you,00:03:15
gentlemen and ladies, gentlemen and ladies who00:03:18
are watching this video. Thank you very much00:03:20
for choosing to watch. first my videos00:03:24
truly and from the heart I say them with my00:03:27
hand here in my heart Thank you very much00:03:29
for choosing to watch my videos first00:03:31
I hope you learn a lot from this new00:03:34
video and I hope you continue learning from00:03:36
the other videos on the channel where I00:03:39
practically transmit what that I know, without00:03:42
profit, I transmit what I know so00:03:44
that you learn and become00:03:47
professionals in this tool. At no00:03:50
cost to you, you only00:03:51
have to watch the tutorial to00:03:53
practice the concepts and that's it. In00:03:56
time, from so much practice, you will become00:03:58
experts in the tool Excel is not useful00:04:01
for everything a big hug I hope00:04:03
you get the most out of this00:04:05
tutorial How to make an00:04:07
automated agenda with a calendar using00:04:09
Microsoft welcome and see you in00:04:13
this tutorial00:04:15
Hello how are you my friends How are you00:04:17
welcome back to my00:04:20
YouTube channel Excel easy fact that00:04:23
your friend and colleague Andrés Rojas Moncada speaks to you,00:04:27
welcome partners to a00:04:29
new Excel tutorial video plus one00:04:32
more Excel tutorial video on my00:04:34
YouTube channel excellent easy and they asked me for00:04:37
something very particular in00:04:39
one's comment box of the00:04:41
Channel's videos and I'm going to give you an answer00:04:42
because it really took me about an00:04:45
hour before I went to work. I want to00:04:47
record what I have fresh How to make a00:04:50
better calendar or agenda How to00:04:53
make an agenda with a calendar or an00:04:57
automated agenda in Excel with a00:04:59
calendar using formulas within00:05:03
Excel and be careful we are going to use a small00:05:06
Macro so that this is very00:05:08
interactive and so that it looks great00:05:11
as you saw at the beginning of the video I don't00:05:14
know if it works I don't know if we should record it00:05:16
today or maybe tomorrow because suddenly today it does00:05:18
n't work out but I'm going to try00:05:20
and I'm going to record it so that you can see it yes00:05:23
Obviously you are listening to this00:05:25
because it already worked out on the first attempt That00:05:28
's my first attempt ready the first thing00:05:30
you have to do is take an00:05:31
Excel workbook and create a small00:05:34
database. Look, I have an Excel workbook that00:05:35
has approximately00:05:37
smoke. A table that has approximately.00:05:39
Well, I'll tell you how many rows it has.00:05:42
At the moment, it has 23 rows, but I think it00:05:44
has much more. Let's go check it out.00:05:47
down 220 rows this is an00:05:50
example database that has three columns look00:05:53
column number one says date now00:05:55
column number 2 says subject and column00:05:58
number 3 says status What am00:06:01
I doing inside this database I am00:06:03
recording the issues of my agenda00:06:06
items on my agenda obviously those are00:06:08
fictitious data from, for example, making a00:06:11
template for a video tutorial00:06:12
studying an Excel function and doing a00:06:15
5 mit exercise with Diana to collect00:06:18
Software requirements that we also create the00:06:21
title and description of a video00:06:22
tutorial well, in short, they are, let's say,00:06:25
things or activities that are being00:06:27
done during the day and that00:06:30
I am recording within the database.00:06:33
This is a table that I have created00:06:36
on sheet number one with that amount00:06:38
of records then I'm going to place the sheet,00:06:41
for example, look like this00:06:43
database sheet, I'm going to give it an enter and00:06:46
immediately I'm going to press the00:06:48
secondary button and I'm going to change the color.00:06:50
I'm a little nervous because I don't know00:06:52
if this will help me. come out first I have00:06:54
everything in my mind I have everything in my00:06:56
mind let's see if it works00:06:58
and I'm going to create a table with this Look00:07:01
I'm going to give it control t control asterisk00:07:03
control t and I'm going to give it an enter I'll create00:07:06
a table Obviously have00:07:08
headers, that is, I have here this00:07:11
little check that I have over here, I have to00:07:13
leave it checked. Look at this little check00:07:15
that I have here, I leave it checked in00:07:17
order to create a data table that00:07:20
contains the headers and I am going to00:07:22
immediately remove the filters00:07:24
using the design of table of the00:07:26
tab that is activated called00:07:28
table design, I will call this table, for00:07:31
example, different from the sheet, it is a00:07:34
database basically that is00:07:36
recording all the activities that I00:07:38
propose or propose to do on a00:07:40
given day. So for example Suppose00:07:42
someone asks me for advice.00:07:44
Then I come and enter it00:07:46
directly into this00:07:48
database and put it, for example, advice00:07:50
with Daniel, advice with a client, such00:07:53
advice with such a person, when are we going00:07:56
to do it, we are going to do it on00:07:58
October 10th or We are going to do it on October 200:08:00
of the year 2022 at 10 in the morning and00:08:03
I placed it pending in the ready state. Look,00:08:05
here I have a column called00:08:07
status, a column of the subject and a00:08:09
column for example of the date. Now00:08:11
suppose, for example, that I go to place00:08:13
here look at an activity we are going to00:08:15
place it for October 3100:08:18
in my system in my system the dates00:08:21
are given as month 10 years That is to say00:08:24
first the month goes then the day goes and00:08:26
then the year goes because my Excel or my00:08:30
system My Regional configuration of the00:08:31
operating system is in American, my00:08:35
laptop is in American. So00:08:37
in this case I have00:08:39
first the month, notice, then I have the day00:08:42
and then I have the year, I have the month of the year,00:08:45
therefore the dates go like this but a00:08:48
date is a date inside like this Don't00:08:49
worry, then suppose, for example,00:08:52
that on October 20, October 21, October 22,00:08:56
or on October 31, I'm going to place them and look,00:08:58
October 31, 2022, at 2 p.m.,00:09:03
which would be two in the afternoon, I'm going to00:09:05
place them like this. Look, 14 hours, I'm going to00:09:07
meet.00:09:09
meeting we are going to place them and look00:09:12
meeting with Pablo We are going to put00:09:15
together meeting with Pablo for00:09:16
template development for00:09:20
template development obviously00:09:23
Excel template for Excel template and I am going to00:09:26
put it pending suppose for example00:09:28
that I am serving a client they ask me00:09:30
for me They ask me why they ask me00:09:32
by email on Whatsapp whatever it is00:09:35
that sets up a meeting00:09:37
So I come here, the objective00:09:39
of this database is to make this a00:09:41
database that contains the agenda00:09:44
so that it can obviously be00:09:46
selected within a calendar and00:09:49
bring you the pending ones as you saw00:09:51
at the beginning of the video and bring you the00:09:54
finished ones and the complete list of00:09:56
activities for that date selecting00:09:58
the calendar ready first00:10:01
important thing you have to do we are going to00:10:02
use a very small Macro that is00:10:05
done or that is executed with the00:10:07
selection Chain event when I make a change00:10:09
in a selection in the sheet or in a00:10:11
sheet or in a cell then I need Yes00:10:14
or yes to enable the macros then this00:10:16
Excel file or the Excel file that00:10:19
you are going to use has to save look00:10:22
file save as like an00:10:25
Excel file enabled for Macro00:10:27
I want you to realize that this00:10:28
file is in the folder that00:10:31
is on the desktop look here00:10:33
little card that is on the desktop that00:10:35
would be 99.2 Look at this folder I00:10:39
saved an Excel workbook enabled for00:10:41
macros here you have it, look at it This is00:10:44
the same book that I have open book00:10:46
992 which is the tutorial to download a00:10:48
spreadsheet enabled for macross00:10:51
when you are going to save it what you00:10:53
have to do is select a path00:10:55
then In this case, you put the name00:10:57
here in file name And where it00:11:00
says type, in this second combo you tell it an00:11:03
Excel workbook enabled for macros.00:11:05
You select this option. You save it as an00:11:08
Excel workbook enabled for macros00:11:09
so that it saves the macros and00:11:12
keeps them. and you can run them obviously00:11:14
the first thing you have to do is that00:11:16
the first thing you have to do is that00:11:18
then it doesn't matter if you don't have the00:11:20
programmer tab active we're going to00:11:22
get the development environment with Ale f1100:11:24
So I'm not going to use it in this video00:11:26
here how to activate the programmer tab00:11:28
But well, what I said, so to00:11:31
leave it complete I come here to00:11:34
and here where it says to customize the ribbon,00:11:36
I said I was not going to do it and I00:11:39
ended up doing it, enable the00:11:41
programmer tab. Look at this tab that I00:11:44
have here. It's called a ready programmer00:11:47
so you activate this tab to00:11:49
access the development environment to00:11:51
tell what I'm going to do now I'm going to00:11:53
insert a new sheet. Look, I insert00:11:56
a new sheet. I pass the database over00:11:58
here. I select it and the new sheet00:12:00
goes away. Let's call it agenda, look why I'm00:12:03
going to call it agenda, obviously00:12:04
because it's going to contain my agenda, so00:12:06
I come here, I'll give it a name, I'll give it an00:12:10
agenda, and I'll put a little color on it.00:12:12
Look at this little blue tab and00:12:15
I have the database as a table.00:12:17
In this case, through the00:12:20
table design it is called db and then I have an00:12:23
agenda that is a blank sheet of paper which I00:12:25
am going to increase the size to 120% so that it00:12:27
fits just like the database and00:12:30
also fits 120%. percent00:12:33
What are we going to do next, I'm going to00:12:35
create a view, so I come here to the00:12:37
view tab to make it much00:12:39
easier for me, I'm going to tell you in New00:12:41
Window to create a new00:12:43
Excel workbook that points to the same address00:12:45
as physical RAM memory that00:12:48
directly accesses this book And in this00:12:50
second window I am going to select the00:12:52
database. Notice if I stop here in00:12:54
Excel I have sheet number one which00:12:56
would be the agenda and in window number 200:12:58
or in the view Sorry number 2 I00:13:00
obviously have the database so I00:13:03
place myself here on this side of the00:13:05
left side I come here on the left side00:13:07
of this view look and I come to the00:13:10
view tab organize everything and I tell it to00:13:13
generate a00:13:15
vertical type window organization so that I can I take it out like this and00:13:18
then I come and increase this zoom to00:13:21
120% so that you can see it and I'm going to make it00:13:23
a little smaller, that is, I'm going to00:13:26
select the folder here so that00:13:28
the desktop can't be seen over there. I come and00:13:30
select this file, look. and I'm going to00:13:32
run it so that it gives me space I00:13:34
slowly I slowly I think that00:13:36
's fine and then I come to this file00:13:39
number one that would be window number00:13:41
one and I come and expand it a tricityco00:13:44
Look so that you have context of what00:13:46
we are doing and we are going to start00:13:49
creating our template. I am00:13:51
doing this completely acapella, I have not00:13:53
practiced it at all and I hope it works00:13:56
out if this tutorial takes00:13:57
an hour, I'm sorry, friend, but this is00:14:00
what it is, I like to do it a cappella00:14:01
so that You see that this has no00:14:03
loss and that this can be done00:14:05
from scratch and from nothing simply by00:14:08
having a good knowledge of Excel and00:14:11
the functions and the tools and00:14:12
they go on and on and on and on and on with this you00:14:15
come out with this you should exit Okay then00:14:18
I'm going to create inside this cell, notice,00:14:21
a small text called month and I'm00:14:25
going to start adding a color. Look, I'm00:14:26
going to add a little color here00:14:28
so that this doesn't go away00:14:31
with a blue background.00:14:33
bold and I'm going to put a line00:14:37
here below And look here is a month00:14:39
that I'm going to load in this month, for00:14:42
example, I'm going to load October November00:14:44
January February March April May June00:14:48
July August September, that is, the 1200:14:50
months of the year, so for that I'm going to00:14:53
place a month here, right now we're going to00:14:55
put it and then I'm going to create a ready year and00:14:58
down here to start I'm going to create the00:15:01
calendar, that is, here I'm going to create a00:15:03
formula that will display the calendar00:15:05
very easily using a dynamic matrix00:15:08
I know You are going to see this video00:15:10
there in00:15:11
2025-2030, yes,00:15:14
I still believe that we are here working hard on00:15:17
Excel videos, teaching and00:15:20
training people through00:15:21
YouTube videos and I know that it will go away. Let's00:15:24
see in 5 6 7 8 10 years ready00:15:27
so here we are going to create a00:15:29
calendar I come here and select or00:15:31
place this cell in the direction of00:15:33
this cell and I place here the word00:15:36
calendar and How many days does a00:15:38
week have because it has 7 so I come and00:15:40
volume 1 2 3 4 5 6 and 7 look at 7 cells00:15:45
I come to the group alignment with wine00:15:49
absolutely nothing happens to the group Source00:15:51
I come and I put a fill of00:15:53
blue white in bold and at once I put00:15:56
an alignment or a line quote or a00:15:59
little border we have the days Monday00:16:02
we have the days Tuesday we have the days00:16:05
Wednesday then Thursday so I'm going to place00:16:08
three letters for each day of the00:16:10
week Friday then Saturday and lastly00:16:14
Sunday you can put them in the order00:16:16
you want and then I'm going to select00:16:19
this I come center alignment font00:16:21
bold and immediately and I'm going to00:16:24
put a much softer fill color of blue on this00:16:26
and I'm going to00:16:28
put a small line at the00:16:30
bottom. Note that the00:16:33
calendar usually has six00:16:34
rows, that is, six rows for accommodate00:16:37
every day how do I know that a00:16:39
calendar has 6 days because I am guided00:16:41
by the operating system calendar,00:16:42
notice it has one row it has two rows00:16:45
it has three rows it has four rows00:16:47
it has five rows and it has six rows00:16:49
well by seven columns because there are seven00:16:52
days I am going to create a formula that00:16:54
allows me to establish the dates according to that00:16:57
calendar in a fairly00:16:59
simple way. I am going to00:17:02
create a matrix of00:17:04
six rows by seven columns by using the sequence function. I00:17:07
am going to give it an enter. And notice that it created them for me.00:17:09
without any problem 1 2 3 4 5 6 and 700:17:13
then 8 9 10 11 up to 14 up to 42 there00:17:17
I have a matrix of 6 by 7 6 row by00:17:19
seven columns what am I going to do now00:17:21
because I am going to take the month that I pass00:17:24
directly through example here in this00:17:27
cell in this cell that I have here and a00:17:29
year because I can00:17:30
also specify a year because this agenda can00:17:33
do events or it can have events or00:17:35
it can have records that contain00:17:37
different years from different months at00:17:40
different times obviously This is for00:17:42
fill it out and make it interactive00:17:44
and make it work not only for one00:17:47
year but for all years.00:17:48
This is basically a00:17:50
relational model with a single table but it00:17:52
works perfectly, so look, I'm00:17:55
going to put it. Here we're going to see if00:17:57
we put it in this case. the year00:17:59
inside this cell we are going to put like this00:18:02
Look year and I am going to copy this same00:18:05
format that I have here using the00:18:08
source command Sorry here using the00:18:10
clipboard command copy format I00:18:12
come and paste it here look and these three00:18:15
cells that I have here I'm going to00:18:16
combine directly and I'm going to create a00:18:19
drop-down list that has the00:18:21
different months of the year ready and here in00:18:24
these other two cells I come and00:18:26
combined them and I'm going to create the year a00:18:29
numerical value that tells me the year for example00:18:30
2021 22 23 is 24 and 25 and we are going to do it00:18:35
with a drop-down list So I am going to00:18:37
select these two cells I come to00:18:39
source I put a colored background like this00:18:42
light blue with in this case a00:18:45
thick top and bottom border so that00:18:48
's fine and I'm not going to put bold00:18:50
I'm going to leave it like this so I select this00:18:53
cell then I come to the data tab data00:18:55
tools00:18:57
data validation look stay with me don't00:18:59
leave me and then here where it says00:19:01
list I'm going to select for example or00:19:04
select no to copy the months of the00:19:05
year as they are, look at January so that00:19:08
this remains literal so that the person does not get00:19:11
lost later that with the00:19:12
formula that we write it should00:19:15
come out we already know in the tutorials How to00:19:17
convert a text string to a00:19:19
date through nesting of a00:19:21
number or a concatenation of a number,00:19:22
very easy. In the video, we are going to00:19:24
see it. I am going to place the month of February here.00:19:27
Look, you have to00:19:29
place them very well. You have to00:19:31
write them very well. You cannot make a mistake.00:19:33
It says January, February. Then I am going to place them00:19:35
like this. Look. March then he placed January00:19:38
February March to April then he placed April00:19:42
May So then I put look there he00:19:45
corrected me I put May then I put June00:19:48
then I put July then I put August00:19:53
then I put September like00:19:58
this look and then he put October in the00:20:01
month we are in October or in the month I00:20:04
'm recording This video then I place00:20:06
November And finally I put December00:20:10
Look, I'm going to click on accept what's00:20:14
going to happen Well, I have it00:20:15
statically and it's how I need it, I'm not going to00:20:17
use a set of cells to00:20:19
place these months I have January I have00:20:21
February I have March I have April I have00:20:24
May I have June July August September00:20:26
October November and December I am going to00:20:29
select, for example October, look00:20:31
here at the interline I am going to fix it in00:20:33
one go I am going to remove this center I am going to00:20:36
place it like this and then Here I go I'm going to place00:20:38
the year using the data tab data00:20:41
tools00:20:43
data validation and then I'm going to create a00:20:45
list and I'm going to place, for example, the year00:20:47
2021. In case I have00:20:49
records with dates from that year 2021, I00:20:52
put 202200:20:55
2023 then I put 2024 And finally00:20:59
I put 2025 I'm going to click on00:21:02
accept I'm going to select the year 202200:21:05
look, it also seems centered to me I come00:21:07
to the start alignment I remove the00:21:09
central alignment in fact I'm going to00:21:11
center it like here or I'm going to leave it00:21:13
like this Look how it is and ready I'm not going to00:21:16
make a calendar that big00:21:17
obviously Here I have a sequence00:21:20
but I'm not going to leave the calendar that00:21:22
big then I have to make it smaller I left it00:21:24
that big because I want to convert00:21:26
this into a date that starts on the day it00:21:29
is of the week this is How to make00:21:31
a calendar using even a00:21:33
formula on the channel I have like 7 videos00:21:35
of that so you can go look for it,00:21:37
however I will explain it to you here I want you to00:21:39
realize that for October00:21:41
2022 the date on the one I am recording This00:21:44
video the month starts on Saturday Look at00:21:47
October 1st of the year 2022 October00:21:50
1st of the year 2022 is a Saturday and00:21:54
October 31st is a Monday therefore I00:21:57
have to take the day of the00:21:59
week into account to to be able to allow and00:22:01
start the corresponding dates.00:22:04
So here at the moment I only have00:22:06
a formula that is telling me a00:22:09
sequence of six rows by seven00:22:11
columns, what am I going to say to this because I am00:22:13
simply going to add a date and00:22:16
the date will be I am going to add by transforming00:22:18
this month that I have as text into the month00:22:21
as a number and the year that I have as a00:22:23
number because I pass it into the00:22:25
date function without problems so that it generates00:22:27
a date. So to create a date00:22:29
within Excel the function is used00:22:31
date, look, it's asking us for the year.00:22:33
So the year where it is is here00:22:36
inside this cell. Look inside00:22:39
this combined cell, I'm going to give it a00:22:41
comma and it tells me Give me the month, so since I00:22:43
have the month in text and I need to00:22:45
transform it into a number What I'm going to00:22:48
do is use the month function and00:22:50
then I take the text string,00:22:53
pass it into the serial number and00:22:55
concatenate it using the symbol of the00:22:57
person on day 1. If I come and evaluate00:23:00
this text string, it says October 1,00:23:02
obviously. For Excel, this is a00:23:05
date. So when I want to calculate the00:23:07
month, it simply tells me that the00:23:10
month of October is October and it00:23:12
is already returning the month as a00:23:14
number and I pass it directly into the month00:23:16
argument of the date function and00:23:18
that's it. and then I come and tell it day 100:23:20
because it is always going to start the00:23:22
calendar on the first day and I close it00:23:24
and evaluate it with enter. Notice now it is00:23:27
giving me or I am adding to each of00:23:29
those sequences or to each of those00:23:31
numbers that they generate a sequence a00:23:33
date that starts on the first of00:23:35
October or on October one of the year 2022,00:23:38
increased it by one and it gives me October 2 of the year00:23:41
2022 October 3 of the year 2022 October 400:23:45
well so if I come and copy this00:23:47
format here with the little brush and I come and00:23:49
paste it here Notice that it is giving me00:23:51
October 2 October 3 October 4 October 500:23:54
October 6 October 7 October 8 to00:23:56
November 12 and I say man but00:23:58
the calendar has to have the00:24:00
date of October Of course because I am not00:24:02
taking it into account The starting day00:24:04
turns out that I need the first of00:24:06
October and that it starts on Saturday. How00:24:08
do I do it? Well, go back with00:24:09
the day of the week. Then I come and with00:24:11
the formula I subtract the position of the day00:24:14
of the week that falls on the first day,00:24:16
then I use the day of week function,00:24:19
which is a function that returns a00:24:22
number from 1 to 7 depending on the day of00:24:24
the week it falls on and the00:24:26
type configuration that I give it. It asks me for a00:24:28
date. Look here, the serial number is00:24:31
a date. I already have the date00:24:32
saved in this expression that I00:24:34
just created by using the00:24:36
date function I come and copy it with00:24:39
control C I paste it inside the00:24:41
serial number with control B I give it a comma and00:24:44
Note that in the type argument He is00:24:46
telling me Look, you are giving me a00:24:48
date I want to find out or I am going to00:24:50
return it to you What is the day of the week00:24:52
but I am going to return it to you as a number00:24:54
if you choose option 2 Monday is the one00:24:57
Tuesday is the 2 Wednesday That's the00:25:00
3 Thursday 4 Friday 5 Saturday 6 and00:25:03
Sunday 7 look This is the option that00:25:07
suits me best why Because I00:25:08
like the week to start on a Monday00:25:10
you decide what day of the week starts00:25:13
for me The week starts on a Monday.00:25:16
Then I select this option with00:25:18
a click on option 2, I come and close and it00:25:21
tells me Look, the first of October of the00:25:23
year 2022, there is a Saturday day,00:25:26
therefore it is a value of 6, as00:25:28
the person is telling me here. calendar Look Monday 100:25:31
Tuesday 2 Wednesday 3 Thursday 4 Friday 500:25:33
Saturday 6 October 1st Well,00:25:37
in this case I am going to go back in this case this date00:25:40
that is generating 6 days for the first00:25:42
cell so that it returns the00:25:44
corresponding date and by increasing it00:25:47
by one unit Saturday falls on October00:25:50
1 of the year 2022, I'm going to give it control Z00:25:53
then I come and evaluate with enter and00:25:54
indeed Saturday is October00:25:57
1 of the year 2022 and it ends in this case00:26:01
on Monday, look at October 31 of the year00:26:04
2022 as I have it in the calendar00:26:06
these dates would be September as I00:26:09
have here and these other dates would be00:26:12
November remember in my date or in00:26:14
my system the date has the month in00:26:16
the first two digits November00:26:17
November November November October the00:26:19
whole month and that's it What do I need00:26:22
now do Well I need to transform00:26:23
that into a day so I come and00:26:25
select the entire set of cells00:26:27
I give control 1 to call the00:26:29
custom cell format dialog box00:26:31
and I come and tell it I only need00:26:34
two digits of D to specify the day00:26:38
there already being one date but I00:26:40
come and using the cell format I00:26:43
change the information presentation mask00:26:45
where I tell it with two00:26:47
digits dd I want to put the two00:26:49
digits of the day Obviously the year, look, I00:26:52
click on accept and I have 26 2700:26:56
28 29 and September 30 first 2 then00:27:00
until October 31 1 2 3 4 5 and 600:27:05
November but I have a date there I00:27:07
'm just showing the days using00:27:09
the number format or the00:27:11
cell format what am I going to do I'm going to select00:27:14
everything I appreciate this very much and I'm going to leave it at00:27:17
the same size as it was on Sunday. Look,00:27:19
we're going to make it one tricot00:27:21
smaller, another little bit smaller. I think00:27:24
it would be fine here so that it won't come00:27:27
out even a little bit. I00:27:30
think it's fine now, look. and now I'm00:27:33
going to add a color to this. Look, I'm00:27:35
going to put a blue color in the Font so00:27:37
that it looks very pretty, and in the last00:27:39
line I'm00:27:41
going to place here where it says00:27:43
font, a bottom line or a00:27:46
bottom border. Look, I already have a calendar00:27:48
What is the idea of this that when I00:27:50
hit a date of the current month or a00:27:53
date that has records here within00:27:55
this database, the00:27:56
elements from outside are brought here in a00:27:59
database as if it were an agenda from that00:28:01
day and that's it, we have the agenda formed,00:28:03
then we can put indicators on it.00:28:05
Of course, we can play with the00:28:07
indicators. And that is possible.00:28:09
I have also thought about how to make something like this with00:28:11
indicators, but for now I need to00:28:13
write a conditional format that00:28:16
allows me to identify what the00:28:18
dates that do not belong to the month that I00:28:19
have selected Look at this month and00:28:22
this year that I have selected00:28:24
So we already did it like this I need00:28:27
to create a conditional formatting rule00:28:29
where this date is different from the month00:28:33
that I have selected here, which in00:28:35
this case It could be the month of October, the00:28:38
month of November, the month of January, the month00:28:40
of February, any month that I have00:28:41
selected that is different from this00:28:44
month that I have within these dates,00:28:45
well, just clarify it for me,00:28:48
then I am going to create a formula here,00:28:50
look at this. It is very very easy, we have already created00:28:52
the sequence formula, I want you to00:28:54
observe it, take a screenshot there,00:28:57
copy it, so we generate the calendar,00:28:59
then if I already have it and I change, for00:29:01
example, here to January. Notice what it generated, it00:29:03
is changing me if I come and change here to00:29:05
March. Notice how The dates change if00:29:07
I put, for example, September. Notice that00:29:10
September has a change here, it starts00:29:13
on Thursday and ends on Friday, Thursday and00:29:16
Friday, so here it starts on a Thursday,00:29:18
notice, and we end on a Friday, then00:29:21
the dates00:29:22
would be in August And they would be in October,00:29:25
so I need to establish a00:29:27
conditional format that allows me to see with a00:29:31
dark black color or with a gray color00:29:33
the difference of the months, then00:29:35
I come and establish through a00:29:37
conditional format formula by using00:29:40
the month function and I tell with KTM this00:29:43
cell that I have here that It's going to bring00:29:44
a text string like I need00:29:46
her to stay still to place00:29:48
conditional formatting throughout this Range00:29:51
I set it with F4 and then I come and with an00:29:54
amperson I add the value of 1 and as I00:29:58
told you a moment ago Well that's that That's that00:30:00
expression was going to say that thing, that00:30:02
expression is going to give October 1. If I come00:30:05
and calculate the month by using the00:30:07
month function, it is going to give me 10 and00:30:09
this was September, so it gives 9,00:30:10
if it were July, it gives 7, if it were August,00:30:12
it gives 8 and I tell you that the month that I have00:30:16
here is the same or it is different, we are going to00:30:20
do it with different, it is different from the00:30:22
month that I am going to spend inside this00:30:25
cell and each of them, when I00:30:27
apply the conditional formatting, if00:30:30
it is different, put it a light color if it00:30:32
is not different, leave it as it is, that00:30:34
is, leave it black I come and copy the00:30:36
expression control c I copy it I give it an00:30:40
escape00:30:41
like this I press the00:30:43
key combination al or r or n to bring up the00:30:46
new formatting rule dialog box if00:30:48
you don't know get home styles00:30:52
conditional formatting new formatting rule I come00:30:55
and I bring it here and I tell you Look00:30:56
in the last item that says use a00:30:59
formula that determines the cells to00:31:01
apply the format I come and I paste it so00:31:03
ready and I come and I tell you Look I I want00:31:05
the font of that letter here in the00:31:09
Font, look, I'm00:31:10
selecting it to be a color, for00:31:12
example red. Let's put a gray,00:31:15
this light gray, I'm going to click on00:31:17
accept, click on accept. And look, look, I already00:31:19
have it, this would be the month of September,00:31:22
look, that's a date, this is a00:31:24
date, and this would be the month of November,00:31:26
ready, all the ones that are left in00:31:28
black would obviously be the month of00:31:30
November, the month of October. Sorry, the00:31:32
month that I have current, if I change it00:31:35
to For example, January, look, then February.00:31:38
Look at how each of00:31:41
the dates on the00:31:43
automatic calendar changes. Ready, then we have00:31:45
October. Look, if I come and change00:31:47
the year, for example, to 2024, well,00:31:50
obviously, This changes00:31:51
dynamically. We are in the year 2022. What am I00:31:55
going to do? let's do I'm going to center all this00:31:56
alignment I come and center here ready so00:32:00
that the calendar is well centered and00:32:02
we already have it okay What are we going to do00:32:05
now let's establish here an agenda00:32:09
for the day like that we're going to take it out here00:32:11
with macros I'll come here and I'm going to give it a00:32:13
width of 5 points. Let's see if this00:32:16
works out. So we're going to place the day's agenda. I00:32:18
'm going to place the00:32:21
two-point agenda like this. I'm going to give it an00:32:25
enter and I'm going to copy this same format00:32:27
with the little brush. that I have in Access00:32:29
in the quick Access tool bar I00:32:31
paste it here and we are going to00:32:35
place, for example, here in this cell00:32:37
a much softer color with an00:32:41
alignment directly here with a00:32:43
thick lower border that contains a date00:32:46
suppose suppose for For example, we are00:32:48
returning the date of October 1,00:32:51
2022, so I come and set October00:32:54
1, 2022. Look, that's a date,00:32:58
October 1, 2028, October00:33:01
1, 2022. I'm going to set00:33:07
the time directly here. Look, I come here and set the time. of the agenda data00:33:10
that would be these agenda data00:33:12
and then I place the subject here. Look at the00:33:17
subject, we are going to set it well00:33:19
and I am going to give it an enter and here I am going to00:33:21
place the ready status so that it can tell me00:33:24
if it is a finished status or pending00:33:25
depending on how I have configured00:33:27
the activity if I have already met with Diana if I have00:33:30
already created the title the description of the00:33:31
video if I have already done the activity then I00:33:33
will finish it if I have not done it then I00:33:34
left it pending and ready I come and I will00:33:37
copy here or I'm going to stretch this00:33:41
right now I'm going to stretch it I'm going to put a00:33:42
bold white background color and I'm going to00:33:45
place these edges and here in the matter00:33:49
I'm going to go a little bit to00:33:51
the right another little bit to the right00:33:53
because here the thing is a little bit00:33:54
big or it's a little long I00:33:58
think it's there like okay Let's00:34:00
make it a little smaller and that's it00:34:02
Look we have it what I'm going to do Well00:34:06
I'm going to make a filter from the00:34:07
database that is, I'm going to select a00:34:10
value that I have according to this date for00:34:12
each of the elements of the column00:34:15
ready because I need it like this to00:34:18
extract the data I am going to use the00:34:19
filter function I am not going to start00:34:21
using functions that are not00:34:23
dynamic matrix because those functions are00:34:25
The old ones are going to leave the market in a very00:34:28
short time so this is pure00:34:30
dynamic matrix and we are going to take advantage of this new00:34:32
calculation engine or this calculation engine00:34:34
that produces Overflow range00:34:37
what am I going to do well obviously I am going to00:34:39
call the filter function because It is the00:34:41
function for let's say by preference to00:34:45
make filters from a database00:34:47
so I come and call the function here00:34:48
look at filtering and I tell it in the first00:34:51
argument called array I am going to call00:34:54
the database so I come and00:34:57
use the name of the database00:34:58
so as not to point to this sheet if I do00:35:00
n't want you to see it and I select the00:35:02
table and tell it I want to filter that00:35:05
database and I want you to00:35:07
even take the date column in the argument.00:35:10
Look at this date that I have here but00:35:12
It turns out that this date has a date and00:35:15
has a time, it has an integer that00:35:17
represents the date and it has a time that00:35:20
represents a decimal part or a00:35:22
decimal part that represents the time and an00:35:24
integer that represents the date. I00:35:27
need to return from this column00:35:29
only the date because here I have00:35:32
a date of the day such an ordinary normal day00:35:35
that I work by00:35:38
clicking within the elements of this00:35:40
calendar so I need to use00:35:43
some type of gadget that allows me00:35:45
to truncate this integer value and this00:35:48
decimal part so that It only returns00:35:51
the integer part. I can do it with the00:35:53
round function or I can do it00:35:55
with the truncated function.00:35:56
For this type of exercises, I really like using00:35:59
the truncate function that truncates an00:36:01
integer value and blows away the decimal part without00:36:04
rounding to any number. decimal but00:36:05
she allows me to put how many00:36:08
decimals I am going to remove, select00:36:10
the truncate function and within this00:36:12
function basically I am going to call the00:36:15
database column called00:36:17
date time so I come here and tell her,00:36:19
Look, the database is call bd I'm going to00:36:23
open it with a square bracket and I'm going to say00:36:25
Give me back the date time field I'll come and00:36:27
close it with a square bracket and it's going to00:36:29
have each of the00:36:32
sequential serial number elements with its00:36:34
integer part and its decimal part, look at this is00:36:37
the sequential serial number this part00:36:39
quote that represents the time I'm going to00:36:42
click here to open the selector this00:36:44
would be the time 44 842 Sorry that would be the00:36:47
date this would be the date and this other00:36:50
little part that is after the point00:36:52
would be the part decimal this represents00:36:56
the time ready I'm going to give you an escape00:36:58
I want you to observe it Here is the00:37:00
decimal point semicolon which is a00:37:02
row change for each of the rows00:37:04
so I come and tell drum that this is00:37:07
to zero decimal positions to zero00:37:11
decimal positions And if I come and00:37:13
evaluate the truncate function here, well,00:37:15
notice that it is going to return a00:37:17
lot of dates, it is going to return a00:37:21
lot of dates without the00:37:22
decimal parts, that is, only the pure00:37:25
integer values. So what I am going to do is What00:37:27
to do is to tell it directly if this00:37:30
date is what I have within this00:37:32
column that I called from the database column00:37:34
date time are the same as the date00:37:37
that I have here then directly let it00:37:39
filter me then I come and close I evaluate00:37:42
with an enter boom and look It is00:37:44
returning results to me, look, it is00:37:47
returning integers and00:37:49
decimal numbers because it is the result of the00:37:50
filter, not the result of the expression00:37:53
of the Formula, ready for you to take into00:37:55
account. This is the filter. So what00:37:58
happens if, for example, there is no data if I00:38:00
I come and place here September 30 of the00:38:03
year 2022 this agenda only has00:38:06
values for the month of October of the year00:38:08
2022 that was an00:38:10
example database if I place September 30 of the00:38:13
year 2022 Well it appears unique to me but if00:38:16
I place in this case September 29 of the00:38:20
year 2022 I get a calculation error00:38:23
Look, this was the arrangement that I00:38:24
basically made to have a00:38:27
previous date here only To test it I00:38:29
had forgotten it and here I did it To00:38:31
test it then suppose that I come here00:38:33
and I am going to put Look October in this00:38:36
case00:38:37
of October 21, let's put it like this in the00:38:40
year 2022. If I come and tell you, Look, I00:38:42
want you to give me dates for00:38:44
September 30, 2022. No, I don't have it,00:38:46
so I have to come and adjust the00:38:48
formula, the result of the00:38:50
filter function. Well, basically I'm going to I'm going to remove00:38:52
this parenthesis and I'm going to give it a comma00:38:54
and I'm going to establish a matrix type expression00:38:56
using some curly braces and I'm going to00:38:59
place it with a couple of meals, for00:39:01
example there is no data or there is no if00:39:04
there is no data. Let's put it like this. Look00:39:06
no data And I am going to place a copy or00:39:11
I am going to make a copy and later00:39:13
using the comma I am going to place a change of00:39:16
column, I paste the text string, one00:39:20
for the time, one for the subject and one00:39:22
for the state, I am going to place a change00:39:24
of column and I paste I come and close the00:39:27
filter function evaluated with enter and it00:39:29
tells me no data no data and no data that00:39:32
is, when the date does not match00:39:35
any date in the base then it brings up00:39:38
no data Because obviously there is no data00:39:39
But if I For example, I put October 100:39:41
of the year 2022. Note that if it is bringing00:39:44
the data Okay, then we already have the00:39:47
filter done. I now need to do a00:39:49
sorting, a sorting of the00:39:51
matrix so that the agenda is00:39:52
obviously ordered by a type of00:39:55
sorting, for example, ascending to00:39:58
start. with the first hours of the day00:40:00
and finish with the last hours of the day00:40:02
ready then I use the00:40:04
sort function I come and call and tell me order00:40:07
the return of the filter function and you are00:40:10
going to order it by the index that is00:40:12
returning the first column that In this00:40:15
case it is done now so I say the00:40:17
first column I give it a comma and the00:40:19
type of sorting is ascending Look00:40:21
I double click I select00:40:23
ascending I00:40:24
evaluate with enter and it practically does00:40:27
the sorting as I need it Okay00:40:29
then I'm going to create a formula that00:40:32
allows me to establish using a00:40:34
conditional format, for example,00:40:37
the plot, a plot, for example, here00:40:38
it starts in row 4. I can do it00:40:40
using the is even function so that I00:40:43
can put a little color on it and not look so00:40:45
lost when I am reading each of00:40:47
the values. So This is very easy,00:40:50
the only thing I have to do is use00:40:52
the functions to look. Now I tell you,00:40:56
please, the function is an even point that00:40:59
returns true if the number00:41:01
that I pass in its argument is even, it00:41:03
returns false if not. It is even, I tell you, please00:41:05
evaluate the row for this cell00:41:09
that I have here or where I created the00:41:11
conditional format, then I come and00:41:13
tell you using the function, it is even,00:41:15
please Call me, call me at the row functions00:41:18
And if I pass it without a reference,00:41:20
then every Every time I execute a00:41:23
formula in a cell obviously from the00:41:25
selected Range, the row function is going to00:41:29
evaluate the row where00:41:31
the function is being run and that's it, it returns the value00:41:34
of the row where the function is being run.00:41:36
If I execute the row here, then I it00:41:38
returns 4 if I execute the row here it00:41:41
returns 6 and I execute the row here it00:41:43
returns 7 and I come and tell her this is even00:41:46
Then she gives me a logical value00:41:48
a boolean value of true or false00:41:51
See if I come and evaluate this this00:41:55
function is even with the row inside00:41:58
12 because 12 is for Look if I evaluate with00:42:00
f9 it tells me true but if I run00:42:03
this formula for example in 13 it will00:42:04
tell me false I need to also evaluate00:42:07
Which cells are empty of these00:42:09
cells that are empty So I'm going00:42:11
to ask you an additional question and I'm going to00:42:13
tell you, for example, in the00:42:16
cell where the displacement is going to be,00:42:18
which of those cells00:42:20
are empty, how do I need to place00:42:21
two expressions that both execute as00:42:24
true, called the function and I tell you00:42:27
the first logical expression is going to be is00:42:30
even and row if the row that is being00:42:31
moved is for and also the00:42:34
value of this cell moved OR moved00:42:37
using conditional formatting is00:42:40
different from an empty which is different from00:42:43
an empty Well that is full then00:42:44
return me a value of true00:42:46
therefore place a plot only on00:42:48
the even rows I come and select this00:42:51
with control c then I give it an escape I00:42:53
select this entire Range of cells00:42:55
notice how here I am not going to00:42:57
select this last cell because I don't have00:42:59
enough time until 22, I don't think there are any00:43:02
more time elements, for example, I don't00:43:04
think they exist in this database,00:43:06
but if they exist, you can drag them00:43:08
down, press o r or n, and go to00:43:12
the new format rule00:43:14
dialog box, use a formula.00:43:16
Determine a cell, paste with control b00:43:19
and we are going to establish, for example, a00:43:21
frame. We are going to put a00:43:23
light-colored fill. I think it is00:43:26
visible there. I am going to click on00:43:28
accept. Click on accept. And look,00:43:31
the frames are already being placed. here directly00:43:33
Look, the plots are being placed00:43:35
so let's put it, for example,00:43:38
October 22 to see what comes out of the year 2022,00:43:41
look. More procedures are being placed00:43:43
ready Look So, since I need to00:43:46
convert this to a time because I already00:43:49
have the date here, well, what a nonsense, well I00:43:51
simply select the column here, I00:43:53
give it control 1 and I come and tell it about that00:43:56
date. Now I need you to00:43:58
change the format in a personalized way and00:44:01
it is hour, two points, minute, minute, that is, it00:44:04
shows me the hour and00:44:06
shows me the minutes, ready. Look, no.00:44:08
absolutely nothing happens I come here00:44:10
I select it alignment I come and center I already00:44:13
have this here pending I have this00:44:15
here the status and I have the matter00:44:17
returned00:44:18
What else am I going to do Let's see if00:44:20
we change the format of this so that it00:44:22
gives us a much more pleasant format control 100:44:25
I have and select this00:44:26
dialog box, I say personalized, we are going to00:44:28
establish a custom type format00:44:30
and I am going to place, for00:44:32
example, Saturday, Sunday, the day of the00:44:34
week. Then I put three times so00:44:37
that it gives me the day of the week and00:44:39
then He placed a comma so00:44:41
that it is like a comma so not a00:44:43
decimal comma but a text meal and he00:44:46
placed day by day so that it can be thrown to me00:44:49
as a number so here It would be Saturday00:44:51
the 22nd for example and he placed a space00:44:54
inside a couple of quotes I put it in00:44:57
closing with a couple of quotes October00:44:59
November December for example whatever month it00:45:02
is then I come and put mom mom00:45:05
mom Moon like this in this case 4 m and I00:45:09
put it here with a couple of meals00:45:12
year year year year like this from the year 202200:45:16
I give it click on accept, look and now it00:45:19
tells me Saturday, October 22, 2022,00:45:22
if I come and put, for example, October 1,00:45:25
2022, you put me on Saturday, October 1 or00:45:28
October 1, 2022, that would be00:45:31
the date,00:45:32
there is a date, there is no a00:45:35
text string there exists a date So the00:45:38
important thing is that you understand that here00:45:41
there is a date Here there is a date Here00:45:43
there is a date but I am00:45:44
representing it as a time here there is00:45:46
text that is returning the formula and00:45:48
that's it What is the trick of The trick00:45:51
of this is to be able to generate using a00:45:54
Macro that tells us, for example, when I00:45:57
touch a Range,00:45:59
when I touch a Range, for example, an00:46:02
element of this Range of this00:46:03
calendar, send the date to this cell,00:46:06
then this year, this month and this year I00:46:10
They allow you to configure the calendar and this00:46:13
calendar when I touch it here in00:46:15
each of its cells every time there is00:46:17
a change in the selection it sends the date00:46:19
directly here and as this date00:46:21
updates the formula the filter is00:46:23
updated and there we basically have our00:46:25
interactive agenda This would be this, then we00:46:27
can get some indicators,00:46:29
I would think, dependent and finished, ready,00:46:32
how do we do this? Well, we open the00:46:34
visual basic editor for applications, I want00:46:37
you to realize that the00:46:40
calendar range is from A500:46:43
to G 10, ready. Look. find00:46:47
from 5 to g10 and that's it, then I00:46:50
'm going to press f11 to open the00:46:53
development environment, look, and then I00:46:56
'm going to hit maximize, I'm going to hit here00:46:58
restore, I'm going to make it smaller,00:47:01
let's make it a little bit smaller,00:47:04
leave it to me box I'm going to do it like00:47:07
here I'm going to take it down a tricycle and I'm going to come00:47:11
here and I'm going to take it like up here.00:47:14
Look, I have sheet 1 which is called00:47:17
agenda and sheet 2 which is called00:47:19
database. I need to place the event that00:47:21
detects the change in the selection of and00:47:24
calendar that is in the agenda,00:47:26
therefore I have to look for the sheet of the00:47:28
agenda that would be sheet number one. I am going to00:47:30
double click00:47:34
on it. I need the procedure to be triggered every time I make a change in the00:47:36
selection then I need the00:47:37
selection change event I select workship and it00:47:40
tells me private selection change Target00:47:44
returns the cell that I am00:47:46
selecting at a certain time00:47:48
the cell or cells that I am00:47:49
selecting at a certain time the00:47:52
Chain selection event by default so I00:47:53
'm going to give it Here Tico and I'm going to00:47:55
write a small sentence, I'm going to00:47:57
tell you if, for example, in this case we're going00:48:00
to see how we do it00:48:02
using the intercept method, I'm going to00:48:05
use the application object to00:48:07
call intercept, look, I'm going to call00:48:09
here the intercept method that does this.00:48:12
intercept method returns me an object00:48:14
of type Range or returns me an object00:48:17
that intersects if there is a crossing00:48:19
between two ranges if, for example, I00:48:22
pass to an element Or I pass to an00:48:25
argument a very large Range and then I00:48:27
pass a Range to it tiny in the00:48:29
next argument if the00:48:30
tiny range is within the big00:48:33
interset it returns an object of type Range00:48:37
if not it returns a notting or it returns a00:48:40
nothing then I come and open it notice00:48:42
and he is asking me for argument number00:48:44
one as of type Range notice an00:48:46
argument of type Range of cells00:48:49
Then I come and tell you, look how I am00:48:51
going to run this event on sheet00:48:54
number one, which is the agenda, obviously00:48:56
then everything that I make reference00:48:58
to the Range is going to be a reference to the00:49:00
cells of this sheet number one then00:49:03
I call the Range object00:49:06
I open a parenthesis and within a couple of00:49:08
meals like this Look inside a couple of00:49:10
quotes I'm going to place the reference00:49:12
from a5 to g10 then I come and00:49:16
place two points g10 from a5 and00:49:20
establish a Range I come and close here and00:49:23
this object of type Range that I am going to00:49:25
place here with a capital letter is00:49:27
returning me a Range that has all this00:49:30
width, look, it has this whole Range of00:49:32
cells as I am selecting it00:49:34
as is here00:49:36
And then the Target parameter that00:49:40
is being declared in the00:49:41
selection header change because it is00:49:43
returning the cell or the set of00:49:45
cells that I have selected00:49:46
so I come here and I give it a comma and it00:49:49
is telling me give me the range 2 because I00:49:51
tell it Look Target is a variable of00:49:55
object that is returning a Range But00:49:57
it can return one or more cells I am00:50:00
always going to return cell number 100:50:02
using the sells property I tell it to00:50:04
return cell number 1 I come and00:50:06
close the intersec method What happens if00:50:10
I make a change and The selection or the00:50:13
active cell is within this00:50:16
calendar range, so intercept is going to00:50:18
return a cell element, that is, it00:50:21
returns an object of type Range,00:50:24
so it is not a notin, so if I00:50:27
select a cell out here, that is,00:50:30
for example, the cell e15 that00:50:33
is outside here e15 enters00:50:35
Target Then e15 is not00:50:38
found inside a5 to g10 intercept00:50:41
returns a notin So I have to00:50:43
validate directly here using the is00:50:45
notting instruction if the00:50:49
returned element is nothing or is a Range and00:50:52
later I give it a then And I give00:50:55
an enter and I already have the instruction ready00:50:58
but it turns out that if I00:51:00
click on it out here it will tell me that this00:51:02
is true that is, if I give it a00:51:04
cell if I place this in the active cell00:51:06
on the outside Well, it is going to tell me00:51:08
that the active cell is outside the00:51:10
Range. It is indeed a notin and it is going to00:51:13
execute the procedure, so I00:51:15
need to execute it when I00:51:17
click on the cell, so if I deny00:51:20
the expression through the not clause00:51:22
Well, basically I am00:51:24
reversing the effect of is notting00:51:28
when an element exists if I the cell00:51:31
Well, it is going to tell me that it is a nothing and it00:51:33
tells me true the cell the00:51:35
interset method returns is notting So it00:51:37
tells me notting if true for a not it00:51:40
returns false and the procedure is not00:51:42
executed But if I place the cell here00:51:44
directly within the00:51:46
intercept Range then intersec returns an00:51:49
object of type Range and tells me it is noting it00:51:52
tells me false but since I am denying it it00:51:54
tells me true enters into the00:51:56
procedure and runs in this case00:51:59
the execution of that line of code that00:52:02
is going to load the date into the other00:52:04
cell so I come here and say in If00:52:07
I close the procedure I00:52:08
give it an enter and basically I come and00:52:11
minimize the window and I tell it that the00:52:13
cell that I have here is going to contain a00:52:15
date. So the value of cell j100:52:19
is going to contain the value of Target point00:52:21
value. I come here to the00:52:24
development environment and I say Range j1,00:52:28
I'm going to select j1 here and I close with a00:52:31
pair. of quotation marks valid dot00:52:33
I call the property valid the property00:52:36
value and I directly assign to the j100:52:39
the value of Target in this case of the00:52:42
selected one of the selected cell00:52:44
then I come and put Target00:52:47
Target00:52:49
dot cells dot value like this Look, it was00:52:55
assigned directly to j1 Ready00:52:58
The difficult procedure is ready This00:53:00
is not difficult I was going to say it is difficult what is00:53:03
going to be difficult This is not00:53:05
difficult a super well understood line of code00:53:07
then the assignment an00:53:10
assignment statement and then the00:53:11
completion of the If from in elche of the00:53:13
builder who makes the decision Range00:53:16
j1 point value is going to be equal to target.600:53:18
point value ready I00:53:20
minimized this and I come and try it See00:53:22
if I come and crash here look how00:53:24
that is working well cool00:53:27
ready look00:53:28
it is working well at the hair So00:53:31
what happens if I want to create for00:53:33
example here an earring let's00:53:35
create an indicator so I come and00:53:37
place here tell me please how many00:53:39
earrings I have I00:53:40
place like this Look earrings I'm going to leave00:53:43
a small space and I place finished00:53:46
finished like this Look matters finished and00:53:50
pending matters I come and select00:53:53
this alignment with wine then I come and00:53:57
copy with the00:53:58
Z control format broom here I made a mistake I'm going to00:54:01
do it manually alignment combine00:54:03
cells this costs 200:54:06
and I put a dark color with a white00:54:09
background in bold and I'll do it I'm going to leave it like this, I'm00:54:12
not going to put anything in it and then I come00:54:14
here and I'm going to create a very very00:54:17
simple formula, I'm going to delete all this here00:54:19
so that the rest of the format doesn't go away00:54:22
and I'm going to give it the same and I'm going to00:54:25
tell you I want you to tell me about this00:54:27
Overflow Range that the00:54:28
filter function and the00:54:30
sort function are generating here. Return me Return the00:54:33
status column, then I am going to call00:54:35
the choose columns function and00:54:37
later in the matrix I am going to00:54:39
select the cell where I am00:54:40
originating the formula and with the00:54:43
pound symbol I select the entire00:54:45
overflow range and tell it to choose columns,00:54:48
please return column number 300:54:50
which would be the status and if it evaluated it with00:54:53
f9 Notice that it says pending00:54:54
pending finished etcetera etcetera00:54:56
this would be for the pending If00:54:59
I ask you then Tell me which of these00:55:01
values are equal to pending then00:55:03
I come and copy the text string00:55:05
pending I00:55:06
close I come and evaluate with f9 and it returns00:55:10
a logical array00:55:12
if I enclose it with an00:55:14
opening and closing parenthesis and multiply it by00:55:16
one, then the logical matrix converts00:55:19
each true value into a value of00:55:21
one and each false value converts it00:55:23
into a zero. I come and with the sum00:55:26
product function it directly added that00:55:28
binary matrix and it returns a value00:55:31
corresponding to 5 pending, look: 100:55:35
2 3, 4 and 5, I'm going to select three cells,00:55:40
I combine this, then I come here to the font,00:55:42
I put a size of 20, I think 2500:55:46
is better than 25 in bold, and then00:55:50
I come and center it like this, look, and I'm going to00:55:53
put a light-colored fill. so00:55:56
that it is well pulled and here I am going to00:55:58
establish another little formula, look very00:56:00
simple, this does not have any00:56:02
difficulty In fact, here in this00:56:04
quote part I am going to put a bottom line00:56:07
like this Look so that it looks very cool And00:56:10
then we copy the format This is going to00:56:12
have again to the choose00:56:14
columns function I choose the matrix where00:56:16
the overflow range is generated I put the00:56:19
pound symbol00:56:20
Return me the column 13 evaluated with f900:56:23
Tell me which of these are equal to00:56:25
finished then I come and place the00:56:27
column or I put the literal value so00:56:29
that it is compared against that column00:56:31
finished and then I come and close here I00:56:35
evaluate with f9 I get my logical matrix00:56:38
I come and I close this and I multiply it00:56:42
by the value of 1 evaluated with f9 I have the00:56:45
binary matrix and then I come and call00:56:47
the sum product function I think that Now00:56:50
this is going to come out This agenda is very nice00:56:51
and so context so I answer00:56:54
this question ready I come and copy this00:56:57
format clipboard copies of format and00:56:59
look how many pending activities I00:57:02
have left 5 for when by Wednesday,00:57:04
October 5, 2022 then I have00:57:07
finished pending and finished I have00:57:11
two activities now00:57:13
suppose for example I am going to remove what00:57:15
I have here and suppose for example00:57:18
that I add a new activity00:57:21
then I come here I remove the00:57:23
grid lines and here I also come and00:57:25
remove the grid lines grid00:57:27
so that this does not hinder us and that's it,00:57:30
look. So this agenda is going to00:57:32
allow you to work like this. What happens if I00:57:35
come or need to, for example,00:57:37
select them from the first of October?00:57:39
Then I come and tell you Here, on the00:57:40
first of October, schedule a00:57:43
calendar with so-and-so such answer00:57:45
comments from the Channel edit a video00:57:47
tutorial edit a video tutorial mid00:57:49
with clients to collect00:57:50
minimum requirements record a video tutorial Well00:57:52
that's something random00:57:54
How many pending I have five How many00:57:57
finished I have one so for00:57:59
example the 7 look at the 7 it's giving me00:58:01
here in In this case an additional element00:58:05
here is giving me quite a few elements00:58:07
from one in the morning until 6 in00:58:09
the morning it didn't give me like one00:58:11
additional element half like three Look here it00:58:13
gave me like two Sorry two00:58:16
additional elements it's telling me the00:58:19
activities that I I have pending 7.00:58:21
See if I come and leave here00:58:23
it tells me there is no data there is no data there is no00:58:25
data there is no data there is no data there is no00:58:27
data there is no data ready suppose for00:58:30
example that I come and register a00:58:32
new activity within look00:58:33
here I select this sheet on October 31 Sorry00:58:37
I come here let's see the one on October00:58:39
31 edit a video tutorial meeting with00:58:43
Pablo for development look here it is00:58:44
so I come here and I'm going to create one for00:58:47
example for November I know that00:58:49
November doesn't have nothing because look like00:58:51
this I select it it tells me there is no data00:58:52
so I'm going to select this we're going to00:58:55
see the 8 Look it's good and I come here and I'm00:58:57
going to select it for example the day00:58:59
I don't know November 3 would be good Look00:59:03
November third00:59:04
I select November 3 of the year 2022 I am going to00:59:08
place a meeting or video tutorial to00:59:11
answer questions00:59:14
answer questions from the Excel Channel00:59:18
made easy we are going to put Excel00:59:20
made easy like this I give it an enter and it is00:59:22
pending ready Look it was inside the00:59:24
table control Z to remove that new00:59:26
record and it would be November 3 Ah, but I didn't00:59:29
set the time so I have to00:59:31
set the time here, for example, let00:59:33
it be at, I don't know, seven thirty in the00:59:35
morning, let's put it like this,00:59:38
730 in the morning. That00:59:40
would be very good. Look, that's more or less the00:59:44
time I've been in. Well, I get to work00:59:45
at around 6:30 But I like to record00:59:47
very early, what happens if00:59:50
I come and play here on this sheet on00:59:52
November 3, notice that now it says 7:3000:59:54
in the morning video to answer00:59:56
Excel questions done easy pending00:59:58
when I finish it I come and tell you ready01:00:00
Look this task is finished01:00:03
look I'm going to give it an enter here it was01:00:06
updated and it already appears finished very01:00:08
cool this agenda very cool It01:00:10
really is an agenda to generate or go01:00:13
place each of the values Yes, according to01:00:15
the selection that I make in a01:00:17
calendar, look at everything with formulas. This01:00:19
is not good, a Macro, but a very01:00:22
small Macro that does not even make01:00:25
a difference. It is the one that basically activates the01:00:26
selection change. But01:00:29
obviously that has nothing to do with a01:00:31
small Macro Practically everything was01:00:33
left with formulas and conditional formatting01:00:35
that is also created with formulas01:00:37
so if I want to change, for01:00:38
example, here the month of November, look,01:00:41
select November of the year 2022, I come01:00:43
and select October 31, I have01:00:45
elements, I select December, Look,01:00:48
there is no data But If I select, for example,01:00:49
November 1st, I don't have data,01:00:51
November 2nd, I don't have data And on Thursday, November 3rd,01:00:55
if I have data, look, I have01:00:57
data for here. So Thursday is November 3rd,01:00:59
I'm going to corroborate it01:01:02
if you look at Thursday, November 3rd and It01:01:05
is giving me correctly if I come and01:01:06
change the year for example and I enter01:01:08
2024 and I come and select this the01:01:11
active cell was on the outside so notice01:01:13
that there is no data according to what I have01:01:15
collected inside the Source so I01:01:17
come to 2022 I enter October look and01:01:21
I start with my calendar for example I01:01:24
don't know 14 7 8 Look at October 8 and there01:01:29
we have all the formulas01:01:30
practically very easy formulas first01:01:33
formula that I am going to give you would be this01:01:35
formula I am going to give you an enter second01:01:38
formula that I am going to give you It would be this one that01:01:40
I have here third formula that I am going to01:01:42
give you it would be this one that I have here the one on the01:01:44
calendar it would be another formula that I have01:01:47
here I will give you a whole one this is01:01:49
pure formulas this is also good This01:01:52
is not a formula this is simply a01:01:53
good format Let's give it the01:01:55
format control 1 here I copy the format01:01:57
control c and I give it an escape and I come here01:02:02
I have the format Look that is the format01:02:03
of the cell ddd comma01:02:06
ddmm of yey that is a cell number format01:02:10
fill lists fill lists01:02:12
this little formula and that's it and a very01:02:16
simple Macro that I already showed you And that would be01:02:18
all the formulas basically Ah well01:02:20
This one has a conditional format Let's01:02:21
put it out here01:02:24
at or no lies we're not going to give it to or01:02:26
we're going to give it here manage rules and I01:02:28
'm going to copy this formatting rule with01:02:31
control C, I'm going to escape it, I'm going to come and01:02:34
paste it here and I'm going to put a01:02:36
small space so that it doesn't run. And01:02:38
this is the conditional formatting formula.01:02:40
Look, I put the01:02:43
active cell here and basically it doesn't do anything01:02:45
Only when I run it directly01:02:47
inside the cell ready and here01:02:50
we also have a small format that is01:02:53
marking the rows so I come here I'm01:02:55
going to give it conditional formatting01:02:57
rule administrator edit rule I01:03:01
copy this conditional formatting rule01:03:03
escape escape and I'm going to paste it like01:03:06
here I'm going to control B I'm going to give it a01:03:09
space so that it is not evaluated and these01:03:12
are two conditional formatting rules, the01:03:13
one here is the calendar one, the one here is01:03:15
the output format one, ready, look. very01:03:18
easy tell me if this is not easy to do I01:03:21
answer the question tell me how to make01:03:23
a small agenda I just01:03:24
created it I said oops this is where it could come out and it's01:03:27
really good it's a little nervous01:03:29
at the beginning of the video but look look01:03:31
how things can be done with Excel an01:03:33
agenda an agenda that contains or is01:03:36
connected to a data source I am01:03:38
placing issues states date time and01:03:41
when I change here look at the01:03:43
calendar because it is simply01:03:45
the update for example01:03:47
November 3 27 28 Notice how they01:03:50
change each time one of the pure values01:03:53
indicators basically a small a01:03:56
small agenda within Excel a01:04:00
small agenda within Excel very01:04:02
easy and very simple well my friends01:04:05
I hope you like this video tutorial01:04:08
remember workbook let's see which01:04:11
workbook it is because I already forgot 99201:04:15
I say this before saying goodbye. I don't know if01:04:18
this is going to be copied. I haven't seen it01:04:19
on the internet. I haven't seen the first01:04:21
person to do it. I haven't seen how to01:04:23
automate this with formulas. Only01:04:25
with formulas. So if you already see it01:04:28
there after October 21 of the year 202201:04:31
Rest assured that they copied it from01:04:33
this channel because this is for all of01:04:36
you So definitely01:04:39
if another person does it after this01:04:41
date they literally copied it But01:04:43
obviously it has to have context of01:04:45
all these formulas I hope nothing I hope01:04:48
they have enjoyed This video It's a01:04:50
little nervous at first01:04:52
but what's wrong Here these little things come out01:04:54
because this is with reason And this is01:04:57
with knowledge of the facts that we can01:04:59
develop this type of exercises my01:05:03
friends Thank you from the bottom of my heart for choosing01:05:05
to watch my videos from the bottom of my heart. I say with my01:05:08
hand on my heart, I shake your01:05:10
hand, I give you a hug. Thank you very much for01:05:13
choosing to watch my videos on any01:05:16
other channel. Thank you for watching01:05:18
the videos on my channel above01:05:19
any other channel. I am truly very01:05:21
grateful to all of you for the01:05:23
support that you are giving me by01:05:25
seeing or having those visualizations and01:05:27
allowing me to learn because many people01:05:30
can see tutorials and many things01:05:31
but they do not allow themselves to learn. Thank you for01:05:35
allowing me to learn from those tutorials and01:05:37
thank you very much for choosing to watch01:05:39
the Canal XL videos first. made easy and01:05:42
thank you very much for supporting this work01:05:44
as I always say my name is Andrés01:05:46
Rojas Moncada and see you in the01:05:49
next video tutorial partners and01:05:52
partnersDescription:
Referencia archivo: Workbook 992 Clic aquí para descargar el libro de trabajo de Excel: https://www.excelhechofacil.com/p/descargas.html En este video te enseño a construir una agenda diaria con calendario en Excel completamente automatizada. Capítulos del video 0:00 Presentación 4:15 Introducción 12:00 Crear el formato de la agenda 15:25 Crear el calendario 29:30 Formato condicional del calendario 32:10 Agenda diaria 40:45 Formato condicional agenda diaria 46:50 Programar el evento SelectionChange 53:30 Indicadores Pendiente y Terminado 01:01:30 Fórmulas usadas 01:04:05 Agradecimientos y despedida Tutorial para descargar los archivos de Excel Hecho Fácil: https://www.youtube.com/watch?v=2lUlKAXuDD4 ¿Quieres patrocinar este proyecto? Cómprame un café aquí: https://www.excelhechofacil.com/p/regalame-para-un-cafe.html Te estaré muy agradecido. • Mugs para la casa o el trabajo (https://teespring.com/stores/excelhechofacil) •
Preparing download options
* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."
** — Link intended for online playback in specialized players
Questions about downloading video
How can I download "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario" video?
http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.
The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.
UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.
UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.
Which format of "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario" video should I choose?
The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.
Why does my computer freeze when loading a "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario" video?
The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.
How can I download "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario" video to my phone?
You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.
How can I download an audio track (music) to MP3 "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario"?
The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.
How can I save a frame from a video "Cómo Hacer una AGENDA Automatizada e Interactiva en Excel con Calendario"?
This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.
What's the price of all this stuff?
It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.