You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"compben" wrote in message
...
excellent!!! thanks, this one worked perfectly.

"T. Valko" wrote:

what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4?

Try this

=IF(COUNT(A1),INT((MONTH(A1)+2)/3)&MID("stndrdth",INT((MONTH(A1)+2)/3)*2-1,2),0)


--
Biff
Microsoft Excel MVP


"compben" wrote in message
...
this formula worked
=IF(A1="",0,ROUNDUP(MONTH(A1)/3,0)

what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4?

I had modified the formula to this:
VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE)

Quarter New Quarter
1 1st
2 2nd
3 3rd
4 4th
but now I still have my original problem now the formula is reporting
#NA
when the date cell is blank.
"Roger Govier" wrote:

Hi

If you do want to show a 0 then use
=IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2))
or if you want the cell to remain blank, then
=IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2))

The reason it is returning Q1, is that empty date cells are taken to
be
00/01/1900, hence they fall into Month 1


--
Regards
Roger Govier

"Compben" wrote in message
...
if cell A1 is empty I want it to report a 0 how can i do this.
thanks
in
advance. I am ccreating a template that calculate quarters but find
that
the
template without dates is hshowing that it is the 1st quarter.

"Gary''s Student" wrote:

With a date in A1:

=ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)


--
Gary''s Student - gsnu200786