Spreadsheet dates in a calendar layout?

stereosympathy · Sep 8, 2007

HI,

I have a sheet pasted below, I am wondering if it is possible to take data and have it automatically laid out in a calendar format. Say all the dates in column G could be sorted into a calendar with reference text from column E. Any help on this would be appreciated.

Unique Print Schedule.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

9

16212

596123

9/4/2007

Judson

JusdonRockets

2

9/10/2007

300

UPSGND

Approved

None

1518-125WHHC

Grommet25pcsONLY!!!!

10

16255

596214

9/5/2007

MCASA1

PhilipWelpSoccer

2

9/10/2007

200

UPSGND

AwaitingApp

None

1525-23WHHL

11

9/6/2007

SuccessPromotions

SpringfieldCardinals

3

9/11/2007

4800

ADVISE

Approved

None

1518-15WHHP

12

16275

9/6/2007

UNICH1

NorthCarolinaFieldHockey

2

9/11/2007

2006

UPSGND

AwaitingApp

None

1118-12DCARBLUE

6SamplestoDonnyDonDon

13

16278

9/6/2007

Wilson

NFLShield

4

9/12/2007

500

UPSGND

Approved

None

1625WHMW

14

SuccessPromotions

SpringfieldCardinals

3

9/12/2007

5000

ADVISE

Approved

None

1518-15WHHP

15

16215-A

596124

9/4/2007

CustomTowels

UPS

1

9/13/2007

150

UPSGND

Approved

None

1118-12WHHR

Hook&GrommetInsureShipment

16

Agua

ExerciseWithAltitude

1

9/13/2007

150

UPS2Day

AwaitingApp

None

1625-25WHHC

17

16304

587476

9/7/2007

GREGRE

GCHSTigres

2

9/14/2007

75

UPSGND

Approved

None

1525-23-BKHL

ShipFromTeamSpiritTowels

18

15750

594266

8/6/2007

GreenBayPackers

ForceProud2BLoud

3

9/17/2007

144,002

UPSGND

Approved

None

1118-12WHHR

None

19

15778

8/7/2007

PPISports

U.S.Cellular/Wisconsin"W"

3

9/18/2007

55,200

ADVISE

Approved

None

1118-12WHHR

usesecondsforextra's

20

15753

593678

8/6/2007

McArthur

ArizonaCardinals

4

9/20/2007

67,006

ODFL

Approved

StrikeSent

1218-13WHFI

6postProToDon

21

16204

9/4/2007

PPISports

MichiganGoBlue

1

9/20/2007

1016

UPSGND

AwaitingApp

None

1118-12PMS109

10towelstoGregCannon6toDon

22

16256

9/7/2007

TimeWarner

BuffaloBills"Billieve

5

9/26/2007

60036

ODFL

AwaitingApp

None

1118-12WHHR

36postprotoDonnyDon

23

8/31/2007

Agua

WreckEmTech

?

10/1/2007

1005

UPSGND

AwaitingApp

None

1518-125WHHC

24

8/28/2007

Agua

GoHogs

3

10/1/2007

1005

UPSGND

AwaitingApp

None

1518-125WHHC

UpComingOrders

stereosympathy · Sep 8, 2007

This is the general concept, the formatting is a little goofy. It is too time consuming for me to manually enter data into a calendar format but this is a production schedule and it would be nice to have this sheet lay this out automatically to give to each person in production to have a clear layout of what is due on what day. Is this even possible?

Unique Print Schedule.xls

A

B

C

D

E

F

G

1

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

2

3

4

5

6

7

8

9

3

4

10

11

12

13

14

15

16

5

JudsonRockets300pc1518-125WHHC

6

17

18

19

20

21

22

23

7

8

24

25

26

27

28

29

30

9

Sheet1

stereosympathy · Sep 10, 2007

Ok, so now its Monday, maybe someone can help me out with this now. Anybody have any ideas?

triff · Sep 14, 2007

Dear stereosympathy
Try this VBA code. Note that the date format may have to be modified in a few places as I have dd/mm/yyyy format here. However, I think it would do the job for you. If anyone has better ways, I would be happy to learn.
You need two sheets, UpComingOrders and CalSheet. CalSheet is a blank sheet.
Michael

Code:

Sub CreateCalendar()
Dim strCalMonth, strYear, varFirstOfMonth As String
Dim strDateTest, strDate, strWotsOn As String

Dim varCalMonth, varDayFirstOfMonth As Integer

Dim lngModYear, varDaysInMonth, lngRow, vday, lngdow, lngdowCol, lngInputRow As Long
Dim lngDay, lngDayVal, lngDayCol, lngDayRow As Long

varCalMonth = Month(Now)
strYear = "2007"
varFirstOfMonth = "01/" & varCalMonth & "/" & strYear ' this is set up in Aus date format dd/mm/yyyy
strCalMonth = "September"
varDayFirstOfMonth = Weekday(varFirstOfMonth, 2) ' the 2 means that we start with Monday as first day of week

' First build the calendar for the month

lngModYear = strYear Mod 4
Select Case varCalMonth
    Case 2
        If lngModYear = 0 Then
            varDaysInMonth = 29
        Else
            varDaysInMonth = 28
        End If
        
    Case 4, 6, 9, 11
        varDaysInMonth = 30
    Case Else
        varDaysInMonth = 31
End Select

Sheets("CalSheet").Select
Range("CalendarArea").ClearContents

Cells(1, 3).Value = strCalMonth
Cells(3, 1).Value = "Monday"
Cells(3, 2).Value = "Tuesday"
Cells(3, 3).Value = "Wednesday"
Cells(3, 4).Value = "Thursday"
Cells(3, 5).Value = "Friday"
Cells(3, 6).Value = "Saturday"
Cells(3, 7).Value = "Sunday"

For vday = 1 To varDaysInMonth
'start of month is day x
' each day builds on that modulo 7
    
    lngdow = varDayFirstOfMonth + vday - 1
    lngdowCol = lngdow Mod 7
    
    If lngdowCol = 0 Then lngdowCol = 7
    lngRow = Int((lngdow - 1) / 7)
    Cells(2 * lngRow + 4, lngdowCol).Value = vday
   
Next

' Now fill in the daily sheets
Sheets("UpComingOrders").Select
lngInputRow = 2
lngDayRow = 0
Cells(lngInputRow, 7).Select
strDateTest = "00/00/00"

While ActiveCell <> "" And Month(ActiveCell) = varCalMonth
    strDate = ActiveCell
    lngDay = Day(strDate)
    lngDayVal = varDayFirstOfMonth + lngDay - 1
    lngDayCol = lngDayVal Mod 7
    
    If lngDayCol = 0 Then lngDayCol = 7
        
    lngDayRow = Int(lngDayVal / 7)
    
    If ActiveCell <> strDateTest Then
        strWotsOn = ActiveCell.Offset(0, -2)
    Else
        strWotsOn = strWotsOn & vbCrLf & ActiveCell.Offset(0, -2)
    End If
    
    Sheets("CalSheet").Cells(2 * lngDayRow + 5, lngDayCol).Value = strWotsOn
    
    strDateTest = ActiveCell
    lngInputRow = lngInputRow + 1
    Cells(lngInputRow, 7).Select

Wend

End Sub

triff · Sep 14, 2007

My code will work on the sheet as shown in the original Post. To make it applicable across all months, it needs a couple on modications.
The following code will work when the current month is October. I need to think about how to fix the front end so that you can run the cose for any month of your choice.

Code:

Sub CreateCalendar()
Dim strCalMonth, strYear, varFirstOfMonth As String
Dim strDateTest, strDate, strWotsOn As String

Dim varCalMonth, varDayFirstOfMonth As Integer

Dim lngModYear, varDaysInMonth, lngRow, vday, lngdow, lngdowCol, lngInputRow As Long
Dim lngDay, lngDayVal, lngDayCol, lngDayRow As Long

' This needs to be revised to become universal for all months

varCalMonth = Month(Now)

strYear = "2007"
varFirstOfMonth = "01/" & varCalMonth & "/" & strYear ' this is set up in Aus date format dd/mm/yyyy
strCalMonth = "October"
varDayFirstOfMonth = Weekday(varFirstOfMonth, 2) ' the 2 means that we start with Monday as first day of week

' First build the calendar for the month

lngModYear = strYear Mod 4
Select Case varCalMonth
    Case 2
        If lngModYear = 0 Then
            varDaysInMonth = 29
        Else
            varDaysInMonth = 28
        End If
        
    Case 4, 6, 9, 11
        varDaysInMonth = 30
    Case Else
        varDaysInMonth = 31
End Select

Sheets("CalSheet").Select
Range("CalendarArea").ClearContents

Cells(1, 3).Value = strCalMonth
Cells(3, 1).Value = "Monday"
Cells(3, 2).Value = "Tuesday"
Cells(3, 3).Value = "Wednesday"
Cells(3, 4).Value = "Thursday"
Cells(3, 5).Value = "Friday"
Cells(3, 6).Value = "Saturday"
Cells(3, 7).Value = "Sunday"

For vday = 1 To varDaysInMonth
'start of month is day x
' each day builds on that modulo 7
    
    lngdow = varDayFirstOfMonth + vday - 1
    lngdowCol = lngdow Mod 7
    
    If lngdowCol = 0 Then lngdowCol = 7
    lngRow = Int((lngdow - 1) / 7)
    Cells(2 * lngRow + 4, lngdowCol).Value = vday
   
Next

' Now fill in the daily sheets
Sheets("UpComingOrders").Select
lngInputRow = 2
lngDayRow = 0
Cells(lngInputRow, 7).Select
strDateTest = "00/00/00"

While ActiveCell <> ""
    If Month(ActiveCell) = varCalMonth Then
        strDate = ActiveCell
        lngDay = Day(strDate)
        lngDayVal = varDayFirstOfMonth + lngDay - 1
        lngDayCol = lngDayVal Mod 7
    
        If lngDayCol = 0 Then lngDayCol = 7
        
        lngDayRow = Int(lngDayVal / 7)
    
        If ActiveCell <> strDateTest Then
            strWotsOn = ActiveCell.Offset(0, -2)
        Else
            strWotsOn = strWotsOn & vbCrLf & ActiveCell.Offset(0, -2)
        End If
    
        Sheets("CalSheet").Cells(2 * lngDayRow + 5, lngDayCol).Value = strWotsOn
    End If
    
    strDateTest = ActiveCell
    lngInputRow = lngInputRow + 1
    Cells(lngInputRow, 7).Select

Wend

End Sub

stereosympathy · Sep 14, 2007

Triff,

This code looks pretty impressive. Which sheet should I put this on and how do I get it to "Calendar"? I have it in UpComingOrders but nothing happens. Thanks a lot for the help so far, thats a lot of coding!!

triff · Sep 14, 2007

stereosympathy
Open VBA with alt F11. In the project for your workbook insert a new module (Insert Module) and paste the code into the module. From UpComingOrders run the macro (doesn't matter which sheet you are in actually) - Tools Macro Macros (CreateCalendar) Run.
Michael

stereosympathy · Sep 15, 2007

Michael,

I have the macro running and it lays out a calendar in CalSheet which is awesome but it doesn't diplay anything from UpComingOrders sheet in the calendar. Do I need to format the cells a certain way to get them to display?

triff · Sep 16, 2007

The macro is working well for me.
I think it may not be reading your input data as being for September. The way it is set up, column G should be in DATE format.
Also check that the Calendar in CalSheet is actually for September commencing on a Saturday and with 30 days as it may think that it is the first Month of the year(ie 1/9 not 9/1).
If you like you can either step through the macro or insert a couple of msgbox steps to determine what is happening.
cheers
Michael

stereosympathy · Sep 16, 2007

You are totally right! It is displaying a calendar for 1/9. I'm sorry to keep asking you questions but how do I change that so it is set to 9/1, not 1/9?

Spreadsheet dates in a calendar layout?

stereosympathy

Board Regular

Excel Facts

stereosympathy

Board Regular

stereosympathy

Board Regular

triff

Board Regular

triff

Board Regular

stereosympathy

Board Regular

triff

Board Regular

stereosympathy

Board Regular

triff

Board Regular

stereosympathy

Board Regular

Similar threads

Forum statistics

Share this page

Spreadsheet dates in a calendar layout?

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock