Excel VBA Introduction Part 55.1 - Working with Dates
HTML-код
- Опубликовано: 22 июл 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
By Andrew Gould
You can download the example code from this page www.wiseowl.co.uk/videos/exce...
www.wiseowl.co.uk - This video explains the basics of working with dates in VBA. You'll learn about the Date data type and how to write unambiguous dates in your VBA code. You'll also learn about Excel's leap year bug and why some VBA dates don't match those in Excel. You'll see how to return the current date and time, as well as various techniques to format dates. The final part of the video describes a variety of date calculations and functions including the difference between DateDiff and DateDif, calculating working days using NetWorkDays and how to calculate age in years accurately.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
this is the best vba course on RUclips 👍👍👍
Thanks so much!
I spent days looking for a function that convert variables into date type. And finally got Dateserial from you. Thank you so much!
the last video in the series........thanks so much for your wisdom.........vba guru.....Andrew Gould
Finally came to learn the last video in your VBA video list on RUclips. Starting from the first video on 15 May 2018 to the last video on 03 Sep 2018. Thank you so much for putting these videos, Andrew.
Took you 4 months to learn all his videos , I must be slow lol
Excellent, thanks so much
You're very welcome Michael, thanks for watching!
Thank you so much for your easy great uncomplicated explanations ;)
High calibre tutorial, many many thanks very useful
You are incredible.
Ahh thanks so much!
Thanks Andrew, very interesting. Added AgeinYears function to my personal addin macros... :)
Thank you so much
You're welcome, thanks for watching!
Great 👍💯
Thanks for watching!
I would like to know abt "Chdir" statement.
Thankyou.
There are two different columns where I need to apply the filter basis two different cell values.
1 greater than equal and another less than equals. I have posted my reply pls help.
Hi Andrew.. I posted my codes in one of the responses from your end.if you have seen that then pls let me know the solution..
Thanks for the useful piece of trivia lol
:D you're welcome, hope you got something useful out of it too!
Thank you for the valuable information. I have a question. I need to calculate hours worked from a table using vba sql where I have a table working hours with columns: name, date , time start, time end. How its posible with sql vba to sum (time end ) - sum (time start) and give me working time over 24hourt. For example 48:18 ? Thanks for answering
Hey Andrew, do you or have you done any videos on creating excel dashboards?
Andrew, thanks so much for the reply. I do very much enjoy your video lessons... you present alot of information not normally covered in other tutorials, which, I love. I enjoy your sense of humor while presenting as well. Also agree that Twilight movies were horrible. lol. Hmmm, Dashboards... I work for a major airline carrier here in the USA and have seen where Dashboards take raw somewhat boring data and bring it life with charts and gauges. I think a real life example from start to finish would be a great series. I so enjoyed your userform videos using your Movies database.
how to set date from activecell.offset (0, -1) ? it is possible?
How we wirte VBA macro for insert data from one sheet1 to next sheet2? It's about 5 veriables that we have to save sheet1 to sheet2?
Yes, it's right. but in sheet 2 where we save record data, have a table limitation. Because every time cursor need to go to last row of data table in sheet 2. for this logic I use offset property but it was not working for 5 variables.
Hi Andrew. Pleaee help in below scenario.
There are two worksheets, A and B. In A range("C19").value= a date, called start date, A range("D19").value = called end date. Sheet b has 7 columns A:G all different headers. First I have filtered in B sheet column A with a criteria 1. Now i need to further filter out all date values in column C which are greater than equal to C19 and less than equal to D19 and then copy all data available from column A:G and paste in Sheet3 of Workbook.
Thanks in advance
Hey Andrew, I will share the entire code tomorrow.
Hi Andrew..there are two steps I followed.
1-
Dim stdt as Date, endt as Date
Sheets("sheet1").range("c4").Value = stdt
Sheets("sheet1").range("d4").Value = endt
Sheets("sheet2").range("A1").Autofilter Field:= 3, Criteria1:=">=stdt" , operator:=xlAnd,criteria2:=">=endt"
Second method
For each cell in sheets("sheet2 ").range("A:A")
If cell.value>= sheets("sheet1 ").range("c4"). Value And cell.value
Both steps didn't work..😢
Hi, how about writing the date in another language?
Secondly , I need a help in writing a code for a date.
One cell has a date.suppose today but that date should not change till 6:00 am tomorrow morning. As soon as the system time is 6:00 am tomorrow the date then can change. Basically need help with time zone code .
Thanks Andrew, will try and share the results with you tomorrow.
Many thanks Andrew.. will try this out today and let you know the outcome..thanks once again.😊
Hello, I'm fighting with this to this day in my project. Finally, I've rewritten almost everything to be defined as SerialDate or by Year Month Day (I've got even Week/Year -> date).
What would you recommend for portable excel, that's depending on Dates and "col, dot" delimiters if in Work I've got Czech Office 2010, home English Office 2017 and my customer has German Office 2013... I'm not joking. My application has to be portable within these three offices :-D
How to save them was the most challenging.
At first, I was saving them to registry SaveSettings() and LoadSettings() but that would save it as string. So when I save them in Czech and load them in English, excel raised an exception. My current (for now working) solution is that:
- Saving to cell as DATE and load from this cells as DATE. Internaly, this is working well.
- If user has to write a date in the form, I've got lblDateNow.caption = "Enter date in format: " & Date() so user sees HOW to write the date. (24.12.2017 for czech, 12/24/2017 for english).
- When I load the date from the cell where it is saved (this cell is in range of columns 1-15 which are hidden), I specifically need to convert that into: Year(), Month(), Day() or Format('ww', Date) for which week.
- If user assign 53/2017 as the date stamp, I convert it by these functions: pastebin.com/pVuk0wm4
Excel really does not make things easier in these types of things... :-)
The DateTimePicker was my first way of doing things. But then I went home and noticed that in newer version of office I would have to download library for that... So it's not cross-platform solution.
Your second thought, about making it into 3 cmb, it think from user perspective, that's not happening. The form has to be "nice". Or at least "some kind of nice". I can't present a form that will be more white (combo boxes) than grey :-) So I show user the right form and then --check-- if the date he entered is in valid format and valid range.
You don't know ho much! I would love the DateTimePIcker. But the excel has to be portable without installing anything. And for that I didn't find any solution.
Hi, to all. I'm trying to insert in a Subroutine a date so that i can call it later to use in others subroutines, i need this because my excel gets data from several souces and for each one i need to insert always the date, by achieving this pretencsion i only need to insert the date one single time. Can any one help. Thanks.
Hi Antonio! You could declare your date as a public constant at the top of any module. For example:
Public Const MyDate As Date = #01/22/2023#
You can then reference MyDate in any procedure in the same project. I hope it helps!
@@WiseOwlTutorials Hi, thanks for the answer.
I've tried it but repalce the #01/22/2023# by a imput box because normally im fetching data from several sources for the previous day or others and not the current day, and the input box is not accepted.
To use the reference MyDate on others procedures must I call it?
Thanks.
Hi Andrew,
Below is the code I've written.
Dim stdt as Date, endt as Date
Sheets("sheet1").range("c4").Value = stdt
Sheets("sheet1").range("d4").Value = endt
Sheets("sheet2").range("A1").Autofilter Field:=3, criteria1:= ">= stdt, operator:=xlAnd, criteria2:= "
Grt to hear from you Andrew..Will try this out and let u know..Thank you for your responses.. You are definitely one grt help..
Hi Andrew, I tried the code suggested however the desired result is not displayed.
Let me explain in a lil detail.
Suppose Range("A1").value = 7/1/2017
Range("B2").value = 12/31/2017
StDate= Range("A1").value
EnDate= Range("B2").value
The sheet where I need to filter has date column (F) beginning 10/1/2016 and colum G has 12/31/2017
If I'm filtering by Range(A1).value which is 7/1/2017 the filter gets applied but there's no data since Column F has date range 10/1/2016 .
Thus we need to apply logic here wether it will be an If condition or loop.
Pls help to build this logic.
I wrote a statement
If Sheets(Sheet2).Range(F:F).end(xlUp).value >= StDate And Sheets(Sheet2).Range(G:G).end(xlUp).value
Hi Andrew..this worked I checked with few data sheets for applying this method in different columns and same column and it worked..many thanks to you..Will seek your help for queries..
please add chapters for this video
Your too fast in explaination