Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Excel Date Formulas

24 views
Skip to first unread message

Mike Bogle

unread,
Oct 31, 2002, 10:53:47 AM10/31/02
to
Is there a way to convert a date format in one cell such as 10/31/2002
into a format like 4Q02 in another cell? (If this could be accomplish
within the same cell, that would be even better!)

Peo Sjoblom

unread,
Oct 31, 2002, 3:11:23 PM10/31/02
to
One way hard coded for 2002,

=IF(D1<"01/01/2002"+0,"Wrong
Year",INDEX({"4Q02";"3Q02";"2Q02";"1Q02"},MATCH(D1,{37621;37529;37437;37346}
,-1)))

where D1 is the cell where you put the date, or else you cane make a table
(and
hide it somewhere on another sheet) where you would put the dates
in descending order in the left column and the 4Q02 strings in the
right column corresponding to the right date..

=INDEX(B1:B12,MATCH(D1,A1:A12,-1))

for 2000, 2001 and 2002

and hardcoded

=INDEX({"4Q02";"3Q02";"2Q02";"1Q02";"4Q01";"3Q01";"2Q01";"1Q01";"4Q00";"3Q00
";"2Q00";"1Q00"},MATCH(D1,{37621;37529;37437;37346;37256;37164;37072;36981;3
6891;36799;36707;36616},-1))


to retrieve the serial number of a date, put a date in a cell and format as
general

Regards,

Peo Sjoblom

Mike Bogle <mbo...@cntus.jnj.com> wrote in message
news:b5a3f3b8.02103...@posting.google.com...

Ron Rosenfeld

unread,
Oct 31, 2002, 3:48:02 PM10/31/02
to

I do not believe you can do it with formatting, so the result will not be
an Excel date but rather it will be a text string.

=ROUNDUP(MONTH(dt)/3,0)&"Q"&TEXT(dt,"yy")

will display as you have specified where dt is an Excel date.


--ron

Norman Harker

unread,
Oct 31, 2002, 5:06:28 PM10/31/02
to
Hi Mike!

This time, I'll increase my caffeine level and finish before I hit the wrong
button for paste.

I'll admit to being embarrassed by the elegant simplicity of Ron's approach.
But here's my one:

=LOOKUP(MONTH(A1),{1,2,3,4,5,6,7,8,9,10,11,12},{"Q1","Q1","Q1","Q2","Q2","Q2
","Q3","Q3","Q3","Q4","Q4","Q4"})&TEXT(A1,"YY")

I post it because although Ron's solves the problem for Quarters based on a
January 1 start, there are many companies that have different year ends and
this "brute force and total ignorance" approach is readily adaptable for
that.

However, looking at Ron's solution, it might be possible to adapt by
adapting the month argument?

HTH

Norman Harker
Sydney, Australia
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:t853su4e6l4rpnjam...@4ax.com...

Norman Harker

unread,
Oct 31, 2002, 4:57:02 PM10/31/02
to
Hi Mike!

I'll admit to being embarrassed by the elegant simplicity of Ron's approach.
But here's my one:

"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:t853su4e6l4rpnjam...@4ax.com...

Ron Rosenfeld

unread,
Oct 31, 2002, 7:28:01 PM10/31/02
to

>However, looking at Ron's solution, it might be possible to adapt by
>adapting the month argument?
>

=MOD(ROUNDUP((MONTH(G1)+n)/3,0),4)+1

where n is a factor related to the quarter starting month.

Thanks for the compliment.


--ron

Norman Harker

unread,
Oct 31, 2002, 8:00:34 PM10/31/02
to
Hi Ron!

"You beauty!" (Aussie expression said with great energy)

Raise that compliment to the power of n.

I'm very much into general solutions / wider applications. The fries
certainly completed the meal.

Don't go any further on this but out of curiosity you might find the archaic
English Quarters interesting.

Lady Day (March 25),
Midsummer Day (June 24),
Michaelmas (Sept. 29), and
Christmas (Dec. 25)

Unfortunately, it's not my weird sense of humour that raises these dates
under the topic of "Quarters." Those dates are the standard rent payment
dates for most English commercial property leases and on occasions the poor
English have to do rent apportionment calculations based on those dates. The
background is from English agricultural leases and church / pagan festivals
that date back to time immoral.

The Scots (just to be different and to maintain their ancient antagonistic
approach to anything English) do something different. They have:

Candlemas (Feb. 2),
Whit-Sunday (May 15),
Lammas (Aug. 1), and
Martinmas (Nov. 11).

But my memory fails as to whether these are rent payment dates in their
commercial leases.

Regards


Norman Harker
Sydney, Australia

"Ron Rosenfeld" <ronros...@nospam.org> wrote in message

news:iji3suonk83a1ssnp...@4ax.com...

Ron Rosenfeld

unread,
Oct 31, 2002, 10:10:32 PM10/31/02
to
On Fri, 1 Nov 2002 12:00:34 +1100, "Norman Harker"
<njha...@optusnet.com.au> wrote:

>Don't go any further on this but out of curiosity you might find the archaic
>English Quarters interesting.
>
>Lady Day (March 25),
>Midsummer Day (June 24),
>Michaelmas (Sept. 29), and
>Christmas (Dec. 25)
>
>Unfortunately, it's not my weird sense of humour that raises these dates
>under the topic of "Quarters." Those dates are the standard rent payment
>dates for most English commercial property leases and on occasions the poor
>English have to do rent apportionment calculations based on those dates. The
>background is from English agricultural leases and church / pagan festivals
>that date back to time immoral.
>
>The Scots (just to be different and to maintain their ancient antagonistic
>approach to anything English) do something different. They have:
>
>Candlemas (Feb. 2),
>Whit-Sunday (May 15),
>Lammas (Aug. 1), and
>Martinmas (Nov. 11).
>
>But my memory fails as to whether these are rent payment dates in their
>commercial leases.

Interesting dates.

The only thing close we have in the US is:

April 15
June 15
Sept 15
Jan 15

(all adjusted for weekends, national, and occasional state holidays) are
the dates when estimated QUARTERLY US income tax payments are due <g>.


--ron

0 new messages