date function

[btb101]

hi,
i have no vb programming at all but i need to know how i can the following:
i need a sheet that when i put in the month and year (any format) it then
displays a row of days for the given month. i.e. sept 2008 would give 30 days
worth of monday to fridays...
hope that makes sense..
really need this one sorting out..
thanks to everyone

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in.

Put a date in A2 and see if it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
Application.EnableEvents = False
If IsDate(Target) Then
lastdayofmonth = Day(DateSerial(Year(Target), Month(Target) + 1, 0))
For x = 1 To lastdayofmonth
Target.Offset(, x).Value = x
Target.Offset(-1, x).Value = Format(DateSerial(Year(Target),
Month(Target), x), "dddd")
Next
End If
Application.EnableEvents = True
End If
End Sub

Mike

 

[muddan madhu]

suppose u have date in A1 put this formula in B1
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

 

[T. Valko]

Is this what you want:

Enter any date in A1. Then list the weekday dates for that month excluding
the weekends?

For example, A1 = 10/27/2008

Then:

B1 = 10/1/2008 (Wednesday)
C1 = 10/2/2008 (Thursday)
D1 = 10/3/2008 (Friday)
E1 = 10/6/2008 (Monday)
etc
etc

If that's what you want...

Enter this formula in B1:

=IF(A1="","",A1-DAY(A1)+1+CHOOSE(WEEKDAY(A1-DAY(A1)+1,2),0,0,0,0,0,2,1))

Enter this formula in C1 and copy across to X1:

=IF(N(B1),IF(MONTH(B1+CHOOSE(WEEKDAY(B1,2),1,1,1,1,3))=MONTH($A1),B1+CHOOSE(WEEKDAY(B1,2),1,1,1,1,3),""),"")