Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change Day order in Excel Calendar

Posted on 2011-09-11
11
Medium Priority
?
3,411 Views
Last Modified: 2012-05-12
Hello

I downloaded an Excel Calendar from Microsoft Templete site and I need to change the order of days.
The calendar starts on Sunday but I need it to start on Monday.

How can I change it to start the 1st day to be Monday and the last to be Sunday?
I've attached it the file.

Thanks    
Excel-Multi-Page-Calendar.xlsx
0
Comment
Question by:mechanicus01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 3

Assisted Solution

by:Thomas_Roes
Thomas_Roes earned 600 total points
ID: 36520008
Nice one. Needs a bit of hacking into the code:

First: the formula in the cell's:
=IF(AND(YEAR(JanSun1)=CalendarYear;Month(JanSun1)=1);JanSun1; "")

This JanSun1 is a named function that heavily relies on the function weekday:
=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATUMWAARDE("1/1/"&CalendarYear))+1

 This function has an optionl parameter that determines the day number. Default is 1 for sunday, but the parameter 2 means 1 is monday.

Change this function (for all months!) like this:

=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATUMWAARDE("1/1/"&CalendarYear);2)+1

Change headers and bingo
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36520118
That's quite a tip!  I was also interested in this, so I built on your tip, creating a macro to help mechanicus01 out.

This app has two macros - one for one-time use, called resetAllFormulas() which searches through all named ranges like MMM (re: month) Sun1, and puts the parameter ",x" inside the named range formula  (x being 1 or 2), using the Application.substitute command, re:

=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATEVALUE("1/1/"&CalendarYear))+1 'old formula
=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATEVALUE("1/1/"&CalendarYear),1)+1 'new formula

From that point, we can use the macro called weekdayMonth() which

1.  prompts the user for 1 (Sunday start) or 2 (Monday start)
2.  Changes the weekday heading on the calendar for selection chosen
3.  Uses the Application.Substitute command, re:


=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATEVALUE("1/1/"&CalendarYear),XXX)+1 'where XXX is changed to what the user has input.

Here's the code:

 
Sub weekdayMonth()
Dim startWkDay As Variant, oldStartWkDay As Variant
Dim strMonthsVar As Variant, oneMonthRef As String
Dim strSunWk As Variant, strMonWk As Variant

Dim i As Integer
    strSunWk = Split("Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday", ",")
    strMonWk = Split("Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday", ",")
    
    strMonthsVar = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")
    startWkDay = InputBox("Enter 1 for Sunday starts, 2 for Monday", 1)
    
    If startWkDay = 0 Then Exit Sub
        
    ThisWorkbook.Sheets(strMonthsVar).Select
    
    If startWkDay = 1 Then
        oldStartWkDay = 2
        Range("B2:H2").Value = strSunWk
    Else
        oldStartWkDay = 1
        Range("B2:H2").Value = strMonWk
    End If
    
    ThisWorkbook.Sheets("Jan").Select
    
    For i = LBound(strMonthsVar) To UBound(strMonthsVar)
        oneMonthRef = ThisWorkbook.Names(strMonthsVar(i) & "Sun1").RefersTo
        oneMonthRef = Application.Substitute(oneMonthRef, "&CalendarYear)," & oldStartWkDay & ")", "&CalendarYear)," & startWkDay & ")")
        ThisWorkbook.Names(strMonthsVar(i) & "Sun1").RefersTo = oneMonthRef
    Next i
    
End Sub
Private Sub resetAllFormulas() 'One-time only - Used to initialize existing formulas to add the ,1 or ,2 for Sunday/Mondy References
Dim startWkDay As Variant
Dim strMonthsVar As Variant, oneMonthRef As String
Dim i As Integer

    strMonthsVar = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")
    startWkDay = InputBox("Enter 1 for Sunday starts, 2 for Monday", 1)
    If Not IsNumeric(startWkDay) Then
        Exit Sub
    ElseIf startWkDay < 1 Or startWkDay > 2 Then
        MsgBox "Enter 1 for Sunday starts, 2 for Monday Starts", vbCritical, "Aborting."
        Exit Sub
    End If
    
    For i = LBound(strMonthsVar) To UBound(strMonthsVar)
        oneMonthRef = ThisWorkbook.Names(strMonthsVar(i) & "Sun1").RefersTo
        oneMonthRef = Application.Substitute(oneMonthRef, "&CalendarYear))", "&CalendarYear)," & startWkDay & ")")
        ThisWorkbook.Names(strMonthsVar(i)).RefersTo = oneMonthRef
    Next i

End Sub

Open in new window


There's a button in the attached spreadsheet, (see range L1 area), to allow the user to change from Sunday/Monday to Monday/Sunday start, respectively.

Enjoy!

Dave
Excel-Multi-Page-Calendar-r1.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36520127
Whooops!  Slight bug.  I had to make the weekday change on each sheet separately - I thought if I grouped the sheets, one change would be all that was required.

See attached,

Dave
Excel-Multi-Page-Calendar-r2.xlsm
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 1

Author Comment

by:mechanicus01
ID: 36520155
I'm not sure I follow you.

I am getting an error  when I changed it for January like this>
=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATUMWAARDE("1/1/"&CalendarYear);2)+1

Say I have an error in the formula: for WEEKDAY(serial_number,return_type)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36520174
Ok - I'm done "doodling"... :)

This last one handles the shading - either Sun/Sat at opposite ends, or Sat/Sun at the far right.


Thomas_Roes provided the thought and the tip to make the right changes, all I did was do a bit of work to save time, so please assign points accordingly.

Cheers,

Dave
Excel-Multi-Page-Calendar-r3.xlsm
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 200 total points
ID: 36520176
You probably need a comma rather than semi-colon - it depends on regional settings.......also DATUMWAARDE hasn't been translated - that should be DATEVALUE too, i.e.

=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATEVALUE("1/1/"&CalendarYear),2)+1

regards, barry
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36520177
mechanicus - I think Thomas_Roes is giving you code for a MAC.  If you don't have a MAC, then use a comma rather than semicolon.

Also, DATUMWAARDE is incorrect for your setup (I believe), it should say DATEVALUE.

The corrected formula would then be:

=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATEVALUE("1/1/"&CalendarYear),2)+1


Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36520178
PS - you don't have to make the changes manually.  My last post with the attachment has the changes in place, and a macro to change back and forth.

:)

Cheers,

Dave
0
 
LVL 1

Author Comment

by:mechanicus01
ID: 36520379
Thanks everyone
I have to pass this calendar around to others so I rather not use anything with macros because I keep getting the waring about macros.

Using this function, changes Jan 2011 to December 2010,  so I changed it to +WEEKDAY and it set correctly.
=DATEVALUE("1/1/"&CalendarYear)-WEEKDAY(DATEVALUE("1/1/"&CalendarYear),2)+1
 so I changed it to +WEEKDAY and its working now.

But that didn't do anything, I'm back at square 1.
Dimille.
How you have it is just how I need it but without the macros.
The week needs to start on Monday and end in Sunday.

Thanks
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1200 total points
ID: 36520470
So, I just saved it on Monday starting, without the macros.

Here you go:

Dave
Excel-Multi-Page-Calendar-r3.xlsx
0
 
LVL 1

Author Closing Comment

by:mechanicus01
ID: 36520487
Thank you!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question