ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Convert date to yyQn (https://www.excelbanter.com/excel-discussion-misc-queries/205522-convert-date-yyqn.html)

Christine[_3_]

Convert date to yyQn
 
how to return a date to YYQN (eg 08Q1, 08Q2)?

thanks




Harald Staff[_2_]

Convert date to yyQn
 
With date in A1

=TEXT(A1,"yy")&"Q"&INT((MONTH(A1)-1)/3)+1

HTH. Best wishes Harald

"Christine" wrote in message
...
how to return a date to YYQN (eg 08Q1, 08Q2)?

thanks





T. Valko

Convert date to yyQn
 
Based on a calender quarter:

With a date in A1:

=TEXT(A1,"yy")&"Q"&CEILING(MONTH(A1)/3,1)

Based on today's date:

=TEXT(NOW(),"yy")&"Q"&CEILING(MONTH(NOW())/3,1)

--
Biff
Microsoft Excel MVP


"Christine" wrote in message
...
how to return a date to YYQN (eg 08Q1, 08Q2)?

thanks






Thomas [PBD]

Convert date to yyQn
 
Christine,
I dont know of a way to return the Quarter in a Custom Format, or any other.
However, there is a formula to use to do this yourself:

=IF(MONTH(A1)<10,IF(MONTH(A1)<7,IF(MONTH(A1)<4,TEX T(A1,"yy")&"Q1",TEXT(A1,"yy")&"Q2"),TEXT(A1,"yy")& "Q3"),TEXT(A1,"yy")&"Q4")

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"Christine" wrote:

how to return a date to YYQN (eg 08Q1, 08Q2)?

thanks





John C[_2_]

Convert date to yyQn
 
Please do not double post your questions.
--
John C


"Christine" wrote:

how to return a date to YYQN (eg 08Q1, 08Q2)?

thanks






All times are GMT +1. The time now is 03:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com