String Maniplation and Date formula

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

+"Estimated as of : "&@STRING(@MONTH(@NOW),0)&"/"&@STRING(@DAY(@NOW),0)&"/"&@STRING(@YEAR(@NOW)+1900,0)

Excel is having a hissy fit with my attempt to convert this QPW string formula to a suitable Excel substitute. I know to substitue fixed for @string. But things like @month@now to me ought to be easy to say month(now), and @Day(@now) ought to translate to day(now), but none of this seems to be helping.

The net result of the above formula should read as follows: Estimated as of 7/20/03, and the date should of course change as the date changes.
 
Hello Bruce:

One simple way:

="Estimated as of "&TEXT(TODAY(),"mm/dd/yy")

Have a lucky day,


--
Eric van Uden
at the foot of the 'bridge too far'
"Bruce Roberson" <[email protected]> schreef in bericht
+"Estimated as of :
"&@STRING(@MONTH(@NOW),0)&"/"&@STRING(@DAY(@NOW),0)&"/"&@STRING(@YEAR(@NOW)+
1900,0)

Excel is having a hissy fit with my attempt to convert this QPW string
formula to a suitable Excel substitute. I know to substitue fixed for
@string. But things like @month@now to me ought to be easy to say
month(now), and @Day(@now) ought to translate to day(now), but none of this
seems to be helping.

The net result of the above formula should read as follows: Estimated as of
7/20/03, and the date should of course change as the date changes.
 
You are very right, I was too abrupt and shortwinded in my answer. I see now
that an expert like Bob would have been more helpful, so I will try to make
amends ;^)

You start off with this from QPW:

+"Estimated as of :
"&@STRING(@MONTH(@NOW),0)&"/"&@STRING(@DAY(@NOW),0)&"/"&@STRING(@YEAR(@NOW)+
1900,0)

A literal translation would be:

="Estimated as of
:"&TEXT(MONTH(NOW()),"0")&"/"&TEXT(DAY(NOW()),"0")&"/"&TEXT(YEAR(NOW()),"0")

Which would yield: Estimated as of :7/20/2003.

In Excel, NOW() and TODAY() are functions and as such come with brackets,
but since their operation is very simple, no arguments can or need be
entered: the brackets are allways empty. NOW() gives the current system date
and time. TODAY() only gives the current system date. When nested in other
functions, the brackets are kept:
MONTH(TODAY()) gives the month from the current system date.

NOW() and TODAY() are 'volatile' functions, meaning they are recalculated on
recalculation of the worksheet, e.g. on opening or on pressing F9
('recalculate').

As TODAY is closer to the target here, You might substitute it for TODAY:

="Estimated as of
:"&TEXT(MONTH(TODAY()),"0")&"/"&TEXT(DAY(TODAY()),"0")&"/"&TEXT(YEAR(TODAY()
),"0")

Which would yield: Estimated as of :7/20/2003

But both formulas call for the same date 3 times, break it up, format it
into text strings and then paste forward slashes i between. I suppose the
Quattor Pro original has the same flaw, but I am even less of a QPW expert
than an XL one! Anyway, a more economical approach calls the date and
formats it the desired way in one go.

Ergo:

="Estimated as of: "&TEXT(TODAY(),"mm/dd/yy")

Which yields: Estimated as of :7/20/03


Hope all this helps to turn your hissy fit into a fizzy hit!

Have a unique day!
 
Relax Eric.

I was just trying to add to your post to assist Bruce should he undoubtedly
have a need in the future. As Bruce said, your answer suited well, but there
is always room for some more.

Bob
 
Bruce,

It sounds to me that you have somehow merged cells D to N.

Go to menu Format>Cells, and select the Alignment tab. If the 'Merge cells
box at the bottom is checked, uncheck it.
 
Back
Top