Custom Header with Formula

R

Randy

I need the code to create a custom header for only one sheet in my workbook.
I've found code on this site but cannot seem to figure out the correct
syntax. Basically, I need text such as "Closed Week of " displayed followed
by the previous week's date such as May 28, 2006. I have the code for the
date: =(NOW()-WEEKDAY(NOW(),1))+1-7 but I do not know how to put the two
together. This vba novice will appreciate any help!

Thanks!

Randy
 
A

Allllen

How about this:

= "Closed week of " & NOW() etc etc

or

=CONCATENATE("Closed week of ",NOW() etc etc)
 
R

Randy

Allllen,

The first scenario worked great! Darn syntax gets me every time!

One more "easy" question: how can I format the date to come out like "May
28, 2006"? Currently I get 5/28/06 11:50:00 a.m.

Thanks!

Randy
 
A

Allllen

Randy,

Sorry for the delay - it is hard to get to a computer sometimes.
I see what you need.
I think you are working in VBA because otherwise I can't see how you would
have got that message out of excel

Try this

"closed week of " & Format(YOUR NOW FORMULA, "mmmm dd, yyyy")

This should take you at least one step closer.
If it is not using VBA, but an excel cell, then you just go on Cells >
Format > as Date, and overwrite with "closed week of "mmmm dd, yyyy

I am hoping you are going to give me a green tick.
If we are not there yet, let me know more about how you are doing and I will
look here again in the next few days.
 
R

Randy

Allllen,

I have not tried your suggestion yet but it looks like it will work. I'm
trying to figure out another problem: I only need this custom header to print
on one sheet in my workbook not all of them. What's the magic syntax for
this?

Thanks!

Randy
 
A

Allllen

Randy,

Headers are specific to individual worksheets anyway.
So whatever you are setting will only apply to the worksheets that you set
it on.
Try it on a new workbook and you will see.
 
Top