Excel Weekday Function
ฝัง
- เผยแพร่เมื่อ 22 ก.ย. 2024
- Excel 2016 Weekday Function. In this video, you will learn how to make use of the weekday function. The weekday function is used to find the day of a particular date. This function can be used in a variety of ways when tracking the day for the date. While dealing with sales data, analytics data, research data building an attendance sheet oh working with order sheets you will find this function very handy.
The function uses two parameters, first one is a mandate which is a serial number that needs to be a date. If you pass a string or a text value in this position it may not work as desired. The second parameter is a return type. This will help you to decide when to start your weekday using return type number values. This one is an optional parameter, and hence, if you do not provide anything by default it will start the weekday from Sunday and goes through Saturday.
Microsoft Website Link for Weekday reference: bit.ly/2BHo8Dk
This video will also demonstrate IsText and IsNumber functions briefly. Don't worry about these functions at this time, they will be discussed in more detail and thoroughly explained in the upcoming lessons.
By the end of this video, you should be able to use the weekday function thoroughly in your Excel projects.
In case you get stuck, write your questions below in the comments section and I will be happy to help you.
I hope that you're enjoying learning Excel through this Excel learning series with Syed. I request you to share these videos with anyone who desires to learn Excel from scratch.
Do subscribe to the channel and enable notifications by clicking on the bell icon to receive updates about new videos as and when they are added to keep up with Excel Learning.
Download the Practice File using: drive.google.c...
Free Excel Data for Practice: OnlineKnowledg...
This applies to:
Excel 2007, Excel 2010, Excel 2013, Excel 2016, Office 365, and above
#msexcel #excelLearningSeries #excel #excel2016 #excelDateFunction #HowWeExcel #weekdays
THANK YOUUUU!! I’M LITERALLY CRYING ON HOW TO DO THIS! LUCKILY, I FOUND UR VIDEO HUHUHU!
that simple conversion from number to "ddd" is so useful and yet so few videos actually mention this. THANK YOU Syed!
Happy to hear that, do subscribe to my channel if you haven't already and enable notifications for updates.
Best Regards,
Syed H I MyKnowledgePortal.com
Thank you sir. A clear succinct explanation. Was so frustrated with all the other videos on the subject with muddy instructions. Kudos to you!
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Thanks Syed. This helped me to count certain days from an excell sheet. I couldn't understand why it didn't count, but after I saw the istext/ is number explanation I managed to use numbers instead of day and wala it worked!!! Thanks Man
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
A great Vid. Syed. Short, clear, and succinct.
Straight to the point and really useful!!
Amazing. From the outside excel can look so difficult. I have subscribed and will continue to use these videos. Great stuff 🙂
Thank you for the feedback and a subscription Richard, it helps in keeping me motivated.
Best Regards,
Syed H
This technique is so valuable. Thank you
thanks a lot
from Egypt😍
Thank you sir . I learnt ddd. Got the solution. Thanks
What do you mean as a proper date... I have it as a proper date and it's not working. I'm trying to use weeknum and its giving me a date in the 1900s instead of a week number
Hey, I am sorry for missing out on your comment. Are you still looking for a solution to this issue?
@@Syedhussainipage I think i figured out this particular issue.
What I can't seem to do is be able to filter the date in tabs by different criteria. I'm trying to do a weekly, bi-weekly, monthly and yearly type situation but I can't find a single tutorial or formula.
@@futuresuperstar4life Thank you for letting me know that. If you could share a sample data, I can take a look at it and see how we can achieve it.
Use this form to submit your sample data file forms.gle/CxaWGeqdugMktWPx6
Best Regards,
Syed H
I am currently using Microsoft Excel 365. When I put 'DDDD' and it didn't work. I used 'TTTT' instead and that brought out the weekdays.
Thank you!
Very Useful. Thank you.
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Thank you it was helpful
Thank you
1 question - if i put dddd, it defaults to Sunday=1, how do i then start the text version of the week with monday i.e. i've used =weeekday(c2,2) format but text isn't reflecting.
thanks for this, very helpful
The weekday function is not working for me, no matter what I do. I tried formatting the return cell value with General and Numbers but the cell keeps returning the error code #Value!. This is how I type the function, on the right side of the date column I typed; =WEEKDAY(A3,2)
Result shows #VALUE!.
I need help, can it be my excel worksheet settings?
I'm using MS Excel 2021
@@dandan9175 please let me know what is in the A3. Also, if you can please share a copy of your data as a sample for my reference.
Use this form to share sample file
forms.gle/DGXWd7Cc9u11GPao9
so good, thanks
I need dates to populate with just monday tuesdays and thursdays till the end of the year can i do that with the weekday function?
thank you so much
It helped me. Thanks.
Hello
I have a data which consist of dates in format 01/01/2022 like that, I need to sum the value of corresponding day example like sunday monday...
Can you please help..
Ex: the number of items sold in weekends but the provided format is dd-mm-yyyy when I try to use weekday and =text convert to day it showing wrong values
How to overcome this problem...
Please help..
Awesome thanks
Happy to help.
Do subscribe if you haven't already for regular updates and learning.
Best Regards | MyKnowledgePortal.com/
Awesome, thank you!
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
THANK YOU
Happy to help.
Do subscribe if you haven't already for regular updates and learning.
Best Regards | myknowledgeportal.com/
I have a question if anybody could please help me with it, I need a column of each Wednesday of the week, if I put one day and drag it, I get every single date, I just specifically want the one day being my payday and also I would like the first of every month as well
Great video Syed. Do you happen to know if the same weekday( ) return type chart you show at 1:54 works in Google Sheets as well? For instance, could I enter "15" for the return type and shift the start day to Friday in Google Sheets? Thanks in advance! I find your content very helpful.
It should work exactly the same way. It worked for me. Give it a try and let me know how it goes.
Why doesn't Excel just fill in the value Tuesday automatically in the example? Why make us go through the trouble of custom formatting? It's safe to assume someone using the WEEKDAY function doesn't want the result displayed as a number even though that's how Excel sees it.
I am sure there is a good reason behind this. The numbers are more friendly when it comes to calculations than text, and that could be the reason behind this. 🧐
It’s not working for me… something must be wrong with the proper date. I’m working on the latest version of Microsoft office
Thank You Sir
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
So simple
Thank you for the helpful feedback Towkle. 👍
Nice
Sir what about the order number,is it dates are converted from the order number? if yes how do you converted that?
Hello Bunai,
Order numbers are independent from dates and are randomly generated numbers for demonstration.
Let me know if you face any issues.
Regards,
Syed H
@@Syedhussainipage .Thank you sir
@@bunaihakkeemtp I am so glad to hear that. Please subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
thank u
THANKS SYED
I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
need to know what is the total number of workdays between two dates - why would one need to know what day a date is?Your description is not aligned with you video!!!!
sorry for very delayed response. I guess you were looking for th-cam.com/video/x7aJ1aJcVuo/w-d-xo.html Please let me know if you are still looking for any help.
Thanks
You're welcome Kazi 👍
thanks bro
Happy to help.
Do subscribe for regular updates and not learning.
Best Regards,
Syed H
It's very simple everything in computer language is number 0 and 1 that's it
WHAAAATTTTT???? NOOOOOOOOOOOOOOK
Anna Namasthe
Sunday.Monday.Colours 🙏
Namasthe 🙏🏻 Anna,
Please let me know if you have any questions. I will be happy to help.
Best Regards,
Syed H
I have created this function to get first date of week as Thursday in ms access form and reports, but this function return first day of week in las month, but I need it will return only current month dates, if current week first day is in last month then retrun first day of week as 1st date of week in current month.
‘current output
‘when 1-10-2018 is selected from date picker it returns 27-09-2018, which is first date of week in last month but I need it will return 1st of month which is selected in textbox.
Function GetFirstofWeek(dtDate As Date)
'GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 1)
' Excel formula for this function which is perfect and may be converted to function as required ‘=MAX(B2-WEEKDAY(B2,14)+1, EOMONTH(B2, -1)+1)
GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate, vbThursday) - 1))
End Function
'For last day of week
Function GetLastofWeek(dtDate) as date
'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))
GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
End Function
In this function last date of week must be in current month, if month end on first date of then week will be closed and last date of week will be 1st date of week or days remaining in current week.
Useful
cool
I have question to ask,
I want to display first and day of week, week starts from Thursday to Wednesday, if the start date of month is on tuesday then first date of week will be tuesday of the current month, same case on last date of week, if week ends on monday then last date of week will be monday of only in current month, without going to next month last week day.
Out expected
For last month
1-09-2018 to 05-09-2018
27-09-2018 to 30-09-2018
For current month
1-10-2018 to 3-10-2018
4-10-2018 to 10-10-2018
11-10-2018 to 17-10-2018
18-10-2018 to 24-10-2018
25-10-2018 to 31-10-2018
Dear Ashfaq,
Here is what you should do to have a custom start day for the week. Set the value 14 in place of the return_type to begin your week from Thursday. See this link goo.gl/AwwKg9 for more details.
Hope you find this helpful.
Best Regards,
Syed H
@@Syedhussainipage
I have created this function to get first date of week as Thursday in ms access form and reports, but this function return first day of week in las month, but I need it will return only current month dates, if current week first day is in last month then retrun first day of week as 1st date of week in current month.
‘current output
‘when 1-10-2018 is selected from date picker it returns 27-09-2018, which is first date of week in last month but I need it will return 1st of month which is selected in textbox.
Function GetFirstofWeek(dtDate As Date)
'GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 1)
' Excel formula for this function which is perfect and may be converted to function as required ‘=MAX(B2-WEEKDAY(B2,14)+1, EOMONTH(B2, -1)+1)
GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate, vbThursday) - 1))
End Function
'For last day of week
Function GetLastofWeek(dtDate) as date
'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))
GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
End Function
In this function last date of week must be in current month, if month end on first date of then week will be closed and last date of week will be 1st date of week or days remaining in current week.