Accessing system clock for future dates

[kar202]

Is there any way I can use the system clock to look ahead into the future and get me information on how many days will be in a certain month, and what day of the week the first day of the month will be?

For example, say I type in "2/2006". I want my Excel macro to go into the system clock and find that in February of 2006 there will be 28 days and the first day of the month will be a Wednesday.

Any pointers? Thanks a lot.

 

[SonOfEmidec1100]

This should do it for you.

Dim date1 As Date
Dim date2 As Date
Dim dayname As String
Dim daycount As Integer

date1 = CDate("01-Feb-2005")
date2 = CDate("01-Mar-2005")

daycount = DateDiff("d", date1, date2)

dayname = Format(date1, "dddd")

 

[Hypetia]

Also,
___
[tt]
Dim D As Date
D = "2/2006"
MsgBox WeekdayName(Weekday(D)) 'which day of week?
MsgBox Format$(D, "dddd") 'which day of week? (alternate)
MsgBox DateAdd("m", 1, D) - D 'how many days in the month?[/tt]
___

Moreover, you don't need macros to do this stuff.
You can use Excel formulas to get this done directly.

Copy-paste the following formulas in an Excel sheet.
[tt]
2/2006
=DATE(YEAR(A1),MONTH(A1)+1,1)-A1
=TEXT(A1,"dddd")
[/tt]
Enter your date in cell A1, the cell A2 will tell you the number of days in the month and A3 will tell you the day on that date.

For more information, see the following forums.
Forum68: Microsoft: Office Forum
Forum707: VBA Visual Basic for Applications (Microsoft) Forum

 

[johnwm]

The last day of any Excel Date month is the same as the zeroth of the next month:

=DAY(DATE(YEAR(G24),MONTH(G24)+1,0))
in a cell will give you the last day (number of days) in the month of the date in cell G24

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

For tsunami relief donations

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts