2004 Calendar

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am creating a calendar for 2004, with one month per page.
Filling in the name of the months manually isn't too
onerous a task, but filling in the dates for each month is.

Is there are formula that could be used to populate the
cells of the calendar with the correct date?

The layout of each month is as follows:
 
I like the look of the one downloadable from Chip's site but I'm
getting a bug report on it. I think it might be a Regional Settings
thing as someone else reported the same error.

http://www.cpearson.com/excel/download.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
You sure it isn't because you don't have the analysis toolpak (and the ATP for
VBA) loaded?
 
Hi Dave!

Oh yea of little faith.

Both are checked but I get the following code highlighted:

Range("StatusCell").Value = Format(TempDate, "mmm d, yyyy") _
& " " & Format(DayOfYear(TempDate), "##0") & _
NumberSuffix(DayOfYear(TempDate)) & " day of year." & _
" Days From Now: " & Format(DateDiff("d", Now(),
TempDate), "##,##0") & _
" (Workdays: " & _
Format(Evaluate("NETWORKDAYS(" & """" & Format(Now(),
"mm/dd/yy") & _
"""" & Application.International(xlListSeparator) & _
"""" & Format(TempDate, "mm/dd/yy") & """" & ")")) & ")"

I suspect Regional Settings and might have a go at it later if Chip
hasn't already got a solution.

But I really like what I see in terms of being able to highlight dates
etc.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Sorry about the faith stuff.

Can you pick apart the strings and see which is blowing up?

(I haven't used this in awhile, but it worked ok for me with my USA settings.

(Yeah, that didn't help at all!)
 
Hi Dave:

(Yeah, that didn't help at all!)

But it did! At least I know it works in the US and you prompted me to
edit the "offending" code:

Revision:

Range("StatusCell").Value = Format(TempDate, "mmm d, yyyy") _
& " " & Format(DayOfYear(TempDate), "##0") & _
NumberSuffix(DayOfYear(TempDate)) & " day of year." & _
" Days From Now: " & Format(DateDiff("d", Now(),
TempDate), "##,##0") & _
" (Workdays: " & _
Format(Evaluate("NETWORKDAYS(" & """" & Format(Now(),
"dd/mm/yy") & _
"""" & Application.International(xlListSeparator) & _
"""" & Format(TempDate, "dd/mm/yy") & """" & ")")) & ")"

All I've done is change the two references to mm/dd/yy to dd/mm/yy

It seems to be working OK now apart from getting my Mum's birthday
wrong! She'll be writing under separate cover.

I did a Google search and found someone else with the same problem and
it didn't appear to be resolved.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I still haven't opened Chip's workbook, but sometimes if you break up those long
formulas into its component pieces, things begin to make sense:


Option explicit
Sub aa()
Dim TempDate As Date
Dim myStr As String

Range("a1").Name = "statuscell"

TempDate = DateSerial(2003, 12, 31)

myStr = ""
myStr = Format(TempDate, "mmm d, yyyy")


myStr = " " & Format(dayofyear(TempDate), "##0")
myStr = numbersuffix(dayofyear(TempDate)) & " day of year."
'no errors on either of these

'But this caused an error
myStr = Format(Evaluate("NETWORKDAYS(" & """" & Format(Now(), _
"dd/mm/yy") & _
"""" & Application.International(xlListSeparator) & _
"""" & Format(TempDate, "dd/mm/yy") & """" & ")")) & ")"

'So I changed it.

myStr = Format(Evaluate("NETWORKDAYS(" & """" & CLng(Date) & _
"""" & Application.International(xlListSeparator) & _
"""" & CLng(TempDate) & """" & ")")) & ")"

'And it seemed to work ok.



End Sub

In fact, what I did was create a string and plop it into a cell. Then I put an
equal sign in front (manually) of it to see if excel yelled:

myStr = Format(Evaluate("NETWORKDAYS(" & """" & CLng(Date) & _
"""" & Application.International(xlListSeparator) & _
"""" & CLng(TempDate) & """" & ")")) & ")"

Range("b1").Value = myStr

The formula looked like:
=NETWORKDAYS("37972","37986")

I thought it was neat that =networkdays() coerced the text into real numbers,
but it seemed unnecessary to ask excel to do it.

next came:
myStr = "NETWORKDAYS(" & CLng(Date) & _
Application.International(xlListSeparator) & _
CLng(TempDate) & ")"

Range("b1").Value = myStr

And that put this string (formula after I added the =) in B1:
=NETWORKDAYS(37972,37986)
And that looked better to me.

So finally, I think that this will work ok:

Range("StatusCell").Value = Format(TempDate, "mmm d, yyyy") _
& " " & Format(dayofyear(TempDate), "##0") & _
numbersuffix(dayofyear(TempDate)) & " day of year." & _
" Days From Now: " & _
Format(DateDiff("d", Now(), TempDate), "##,##0") & _
" (Workdays: " & _
Format(Evaluate("NETWORKDAYS(" & CLng(Date) & _
Application.International(xlListSeparator) & _
CLng(TempDate) & ")")) & ")"

=======

But since you have the ATP (for both excel and VBA) loaded, you could put a
reference to the ATP (VBA side=atpvbaen.xls for me) and eschew (gesundheit) the
evaluate() stuff and just use it directly:

Range("StatusCell").Value = Format(TempDate, "mmm d, yyyy") _
& " " & Format(dayofyear(TempDate), "##0") & _
numbersuffix(dayofyear(TempDate)) & " day of year." & _
" Days From Now: " & Format(DateDiff("d", Now(), TempDate),
"##,##0") & _
" (Workdays: " & _
Format(NETWORKDAYS(CLng(Date), CLng(TempDate)))



These were my test functions. These return values! Good enough for my testing.
Function dayofyear(mydate As Date) As Long
dayofyear = mydate - DateSerial(Year(mydate) - 1, 12, 31)
End Function
Function numbersuffix(myNum As Long) As String
numbersuffix = Format(myNum, "000") & " "
End Function

========
The real question is: Could you pick the comments out of this mess of a post?


Norman Harker wrote:
<<snipped just to make it less ungodly! (ooh. another faith refence!>>
 
Hi Dave!

Whilst my amendment got calendar to work on my computer settings, I
think that yours are of general purpose use.

Copy to Chip?

eschew (gesundheit)
ROTFLMAO

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
If you want to pick the fly specs out of the pepper, it's ok with me (to suggest
to Chip, that is).

Maybe your "final" version????
 
Hi Dave!

I've written to Chip.

I'm beginning to think that the problem lies with NETWORKDAYS and my
settings which are a tad unusual.

I use English (US) but my preferred short date form is set at
dd-MMM-yyyy with a - separator.

I use that form as it is one of the unequivocal date formats for the
purposes of making sure we are understanding what day, what month and
what Century. But it is a mix and match of English / US systems and
that may be causing the code gaskets to blow.

I corrected the error with my Mum's birthday and she's happy now.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I put a new version of the Calendar file on the web site that
should work properly.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


Norman Harker said:
Hi Dave!

I've written to Chip.

I'm beginning to think that the problem lies with NETWORKDAYS and my
settings which are a tad unusual.

I use English (US) but my preferred short date form is set at
dd-MMM-yyyy with a - separator.

I use that form as it is one of the unequivocal date formats for the
purposes of making sure we are understanding what day, what month and
what Century. But it is a mix and match of English / US systems and
that may be causing the code gaskets to blow.

I corrected the error with my Mum's birthday and she's happy now.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Dave Peterson said:
If you want to pick the fly specs out of the pepper, it's ok
Click to expand...
with me
(to suggest
to Chip, that is).

Maybe your "final" version????
Click to expand...
settings,
Click to expand...
 
Hi Chip!

Great! I've downloaded it and it works very sweetly.

As it's that time of year approaching here's a one page full year
calendar to DIY with formulas from Bernie Deitrick:

2004 in cell A1

In Cell A2:A4 use the formula

=IF(MONTH(DATE($A$1,INT((ROW()-1)/4)+1,COLUMN()))=MOD(INT((ROW()-1)/4)
,12)+1,DATE($A$1,INT((ROW()-1)/4)+1,COLUMN()),"")

Format A2 for custom: mmmm yyyy
Format A3 for custom: d
Format A4 for custom: ddd

Copy A3:A4 to A3:AE4

Copy rows 2:5 (row 5 is still blank) as far down as you want.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Chip Pearson said:
I put a new version of the Calendar file on the web site that
should work properly.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Back
Top