In the YouTube tutorial from teachexcel.com "Automatically Timestamp Data Entries in Excel" published on Jun 26, 2018. In the VBA process, how do I just do the time only instead of date and time? I know that you can just change the format but I need to process a COUNTIFS, and unfortunately the date is messing with the formula.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Input Time instead of Date using VBA/Macro - from YouTube Tutorial Example
0
Post Edited
Title: Title was not descriptive.
Answers
0
Use Time in the vba instead of Now
Example:
Sub current_time()
ActiveCell.Value = time
End SubIn the file to which you referer (Timestampe Entries in Excel) change this line:
myDateTimeRange.Value = Nowto this:
myDateTimeRange.Value = TimeAlso, I changed your title, please make it describe the issue next time)
0
If you ever have to count times past midnight, meaning where 1:00AM is later than 10:00PM, you will need dates. To prepare for such an eventuality you should keep recording date/times and tweak your COUNTIFS to accommodate that format while indeed solve the time display with cell formatting.
In this task it will be helpful to know that Excel accounts for days with integers and for times with decimal fractions. For example, today is day 43822. Since 43823 will be tomorrow the intervening 24 hours are fractions of 1. One hour = 1/24. Excel calculates this very exactly, down to a fraction of a thousandth of a second.
When you record times the integer is removed. 0.25 = 6AM (a quarter of a day) but 43822.25 is 6AM on Dec 23, 2019. Using COUNTIFS you can extract the times between 0.25 and 0.75 (6AM and 6PM) or 43822.25 and 43822.75 with no difference in the result. But extracting the time between 9PM and 3AM from a list of times only requires a huge effort. It's easy and straight forward if you have date/times.
The trick is to include the date in your COUNTIFS. =DATEVALUE("23/12/2019") will return 43822 which equals a date/time value of 43822.00, meaning 12AM. You might use this as the start time for your COUNTIFS. If the date isn't readily available in your lists you can extract it from any date/time value using the INT() function, such as =INT(NOW()) or =INT(A1) if A1 contains a valid date/time (or date) value.
Another method of constructing a date is offered by the DATE() function. =DATE(2019,12,23) writes 43822 to the cell. This is particularly useful if you need the first day of a month, like, =DATE(2019,12,1) or the last day, =DATE(2019,12,0). Here zero indicates the day before the 1st day of the specified month. =DATE(2020,3,0) will return 43890 which is February 29, 2020.