| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date function
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
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 "btb101" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
suppose u have date in A1 put this formula in B1
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) On Sep 13, 10:10*pm, btb101 wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
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) ,""),"") -- Biff Microsoft Excel MVP "btb101" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date function in ACCRINTM requires date format not available | Excel Worksheet Functions | |||
| MAX figure within a date range as a function of today()'s date | Excel Worksheet Functions | |||
| SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Linear Mode
