0:02 in this video we will learn how to work
0:04 with date functions in Google Sheets
0:06 let's start from task number one where
0:08 we may see just the numbers on ABC
0:10 columns and our task is to convert them
0:13 in the format of date and time first
0:15 let's highlight the given numbers on a
0:17 column and in order to convert the given
0:20 numbers on date format let's go to
0:22 format choose H
0:25 date and in order to convert the given
0:27 numbers in Long date format the same
0:30 steps however we will choose here custom
0:32 dat and time and from the given list you
0:35 may choose here long date format
0:38 apply okay and last one to convert the
0:40 given numbers in the format of time
0:44 again numbers select from the given list
0:47 time in this example let's learn how to
0:49 find today's date by just applying the function
0:51 function
0:54 today after that let's learn how to get
0:56 today's time as well let's apply the
0:58 function now enter button that's the
1:02 time and now let's learn how to extract
1:06 from given date a day month and year
1:09 equal sign as a name says we will apply
1:11 H day function okay and as an argument
1:14 give the following first day press the
1:16 enter okay then I will just copy down my
1:19 formula the same step with the mons
1:22 equal to the function name
1:26 months press the enter okay so in the
1:29 following date examples the first comes
1:32 month and then comes date and then comes
1:35 year so here you can see 11 it is
1:38 November the same with the year equal sign
1:45 here okay here we are next step how to
1:48 extract day and month names equal sign
1:51 we have to apply here the function text
1:53 argument it's our date and I would like
1:58 to return day name in the format of DDD
2:01 and this four means you'll get the day
2:05 name in full format in case if I will
2:07 apply just three days and then I will
2:09 get the shorten version of day
2:13 names the same with the mons equal sign
2:17 text argument and then in the full
2:19 format of
2:21 months okay the last two columns we have
2:25 to first identify here weekday and then
2:28 uh identify is this day weekend or
2:30 weekday so equals find we have a
2:32 function named
2:36 weekday with the address of date press
2:39 the enter the output is basically for
2:41 international format where weekday
2:44 starts from Sunday uh in my case it has
2:46 to start from Monday next to your first
2:49 argument if you'll indicate number two
2:52 which means your week starts from Monday
2:54 not from Sunday and then I will copy
2:56 down my
3:00 formula okay is this day is weekend or
3:02 not we will just use IF function if my
3:05 Wick day is bigger than number
3:07 five then it is
3:15 weekend otherwis this is no so number
3:18 six it means Saturday weekend Saturday
3:20 again and Sunday weekend the rest of
3:22 them they
3:25 weekday okay the reverse steps we have a
3:28 year month and day in order to get a
3:31 date again we will use the function date
3:36 year comma months comma and date the
3:37 same with a Time
3:39 Time
3:44 Time hour minute and
3:46 second okay in the third example let's
3:49 learn how to calculate finish date for
3:51 specific task where we have start date
3:54 and work days in order to calculate
3:57 finish date we have to consider holidays
4:00 in our country we have like basic New
4:02 Year Holidays international women's day
4:06 n holiday and so on in order to get my
4:08 finished date let's apply function
4:10 workday where this function will
4:13 consider three arguments which are start
4:17 day number of days and hold days first
4:22 start day comma work days comma and then
4:26 I have to highlight the range of all my
4:29 holidays okay if I will start this task
4:32 on this date and if it gives me 14 days
4:35 to complete my finish date will be 22nd
4:38 of January okay if you will add for the
4:41 first day of January last 14 days it
4:44 might be 15 days uh however we got
4:47 number 22 which means the function uh
4:50 removed all Saturdays and Sundays plus
4:52 if in this period of time we have a
4:55 holidays as we have mentioned here it
4:57 will be again extract uh before to copy
5:00 down you have to make sure the range
5:03 where we have applied uh as our holidays
5:06 has to be an absolute reference now Vice
5:09 Versa steps again we have list of tasks
5:12 to complete we have start date end date
5:15 and then my task is to Define how many
5:18 days I have to complete the given task
5:20 okay to get the reverse tab I will apply
5:23 the function Network days where the
5:26 function take start date and date and
5:30 holidays as well start date comma and
5:33 date if you will give only two arguments
5:35 you will get also the network days but
5:38 it is not considering your holidays so
5:41 let's go back and add the third argument
5:43 which is our holidays
5:45 range so as you can see number six
5:48 turned out like number four uh which
5:51 means between these two days we have a
5:58 formula okay in the first task let's
6:02 learn how to calculate the age so let me
6:05 see here the student's name a birth date
6:08 and today's date using the function date
6:12 if let's calculate age date if will take
6:15 three arguments again start date end
6:18 date in unit start date is going to be
6:22 our birth date comma end date is today's
6:24 date unit it's in the format of for
6:27 example if you'll indicate the why it
6:31 means ear so the students age is
6:35 23 uh if I will indicate the D it means
6:45 dates let's copy down the
6:49 formula so we get some value mistake
6:51 which means we have
6:55 to uh make a constant the given range of
6:58 B3 which is our today's date this one
7:01 let's return B three in absolute
7:08 format okay the fourth example is about
7:11 low netization schedule we may see here
7:14 payment number payment day column is the
7:16 empty which we're going to fill out now
7:20 so we may see our monthly payment
7:23 45,000 interest paid principal
7:26 production and so on so the loan period
7:29 is for four years start date it's the 30
7:33 1st of January and
7:37 2023 uh and total number of period is 48
7:41 months so my task is to now fill out the
7:44 b column where we will indicate payment
7:47 dates which is last states of every
7:49 month okay the problem has to be solved
7:53 with EO month function first of all our
7:56 first payment dat it is already given
7:58 for us I will just give the address
8:01 second pay payment will be the last day
8:05 of next month since it's the January
8:08 next month has to be February so uh the
8:11 E month takes two arguments first is a
8:15 start date which is our January comma
8:17 and then it says months as a month if
8:20 you will indicate number one so you're
8:22 going to get February if you will
8:25 indicate number two what comes the as
8:27 the payment date will be March which is
8:30 incorrect so that's why every time our
8:33 month has to be increased by number one
8:36 and then I will just copy down so you
8:39 can see here is the last day of March
8:42 April May June July and so on so let's
8:44 copy down the formula till the end of
8:47 our loan
8:50 period so you may see here our payment
8:51 started on
8:54 2023 and the last payment will be on
8:58 2026 and last example is to learn how to
9:00 apply e dat function which allows us to
9:04 calculate future or past dates equal
9:08 sign e date so it takes two arguments
9:10 again start date and month so this is
9:19 month okay the same with the second one e
9:20 e
9:22 date again start date and now we're
9:25 going to add three
9:35 months okay so look at the example it
9:38 was January February and then plus three
9:42 it will be April plus 10 will be
9:47 November let's do the P dates Ed E
9:51 dat start date minus
10:05 okay in the short video we've learned
10:08 how to apply basic simple date functions
