Date Formats in Excel

  • Thread starter Thread starter Bhavini Mistry
  • Start date Start date
B

Bhavini Mistry

Hi,

This may sound really simple but I seem to be having a big
problem with it.

I have a list of call logs (from our call centre) and the
dates they came in. I would like to use a function to
automatically calculate which quarter the log came in. I
have tried using the Format function which i found in the
online help but this is not working.

Any help is much appreciated

Thanks
 
A Big 5 guy having problems with such a thing?


=ROUNDUP((MONTH(A1)/3),0)

or

=CHOOSE(ROUNDUP((MONTH(A1)/3),0),"1st quarter","2nd quarter","3rd
quarter","4th quarter")
 
The following formula will work but it took me awhile so
there is probably an easier way.

=IF(OR(MONTH(C1)=1,MONTH(C1)=2,MONTH(C1)=3),"Qtr 1",IF(OR
(MONTH(C1)=4,MONTH(C1)=5,MONTH(C1)=6),"Qtr 2",IF(OR(MONTH
(C1)=7,MONTH(C1)=8,MONTH(C1)=9),"Qtr 3",IF(OR(MONTH(C1)
=10,MONTH(C1)=11,MONTH(C1)=12),"Qtr 4",""))))

If anyone has a better solution??

Judith
 
One more solution, which is overly lengthy but still reliable is to just use
CHOOSE;

=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4))

Luckily since the problem only deals with numbers 1-12, the ROUNDUP solution
worked well.. and there are only 12 tests that can be done on it, and it
passed them all.
 
Thanks so much - that's great!
-----Original Message-----
One more solution, which is overly lengthy but still reliable is to just use
CHOOSE;

=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4))

Luckily since the problem only deals with numbers 1-12, the ROUNDUP solution
worked well.. and there are only 12 tests that can be done on it, and it
passed them all.








.
Click to expand...
 
Back
Top