Calculate a quarter
Hi
It should be
=IF(F262="","",VLOOKUP(INT((MONTH(F262)-1)/3)+1,Lookup!$A$1:$B$4,2,FALSE))
--
Regards
Roger Govier
"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
|