Formatting dates Coming up with odd values

[psulions83]

Hi All,

I am trying to input dates into a sheet in QTR/YEAR format. Q2 would be 02/2017 and so forth. When I do this I get 0.000991571641051066 as the value. I am going to need to upload these to a database and this value is going to have the wrong output. I used a custom format of 00/0000 and that shows me the correct display but when clicking into the cell I get the long decimal value. Is there a way around this besides inputting the cells different?

Any help is much appreciated! I uploaded an image of the results.

 

[rlv01]

that works well except for the ones that simplify the fractions. ie 04/2006 to 02/1003.

You must have a very old company if you have records from the year 1003. Can you give me a realistic example of that kind of conflict that might be found in your data?

 

[psulions83]

I should probably explain myself better.

I took a unique listing of the quarters and put in =TEXT(B3,"00/0000"). This worked great for all time periods minus the ones that could be simplified. So Q4 2006 which was 04/2006 simplified to 02/1003. Excel is simplifying it before it converts.

 

[rlv01]

I should probably explain myself better.

I took a unique listing of the quarters and put in =TEXT(B3,"00/0000"). This worked great for all time periods minus the ones that could be simplified. So Q4 2006 which was 04/2006 simplified to 02/1003. Excel is simplifying it before it converts.

I get that, but what I am asking you if it matters for the practical effort of converting the data you have back into text? You could handle it with conditional fix-ups, though the formulas get pretty long* :

Book1
	A	B	C	D	E	F	G
1				Cell Format		Cell Format
2	Quarter	Year	Raw number	"00/0000"	Data Type	General	Data Type
3	1	1900	0.000526316	01/1900	<= Double	01/1900	<= String
4	2	2017	0.000991572	02/2017	<= Double	02/2017	<= String
5	3	2017	0.001487357	03/2017	<= Double	03/2017	<= String
6	4	2017	0.001983143	04/2017	<= Double	04/2017	<= String
7	1	2017	0.000495786	01/2017	<= Double	01/2017	<= String
8	2	2017	0.000991572	02/2017	<= Double	02/2017	<= String
9	3	2017	0.001487357	03/2017	<= Double	03/2017	<= String
10	4	2002	0.001998002	02/1001	<= Double	04/2002	<= String
11	1	2017	0.000495786	01/2017	<= Double	01/2017	<= String
12	2	2006	0.000997009	01/1003	<= Double	02/2006	<= String
13	4	2006	0.001994018	02/1003	<= Double	04/2006	<= String
14	4	2008	0.001992032	01/0502	<= Double	04/2008	<= String
Sheet1 (3)

Cell Formulas
Range	Formula
C3:C14	C3	=A3/B3
D3:D14	D3	=C3
F3:F14	F3	=RIGHT("00" & ((VALUE(LEFT(TEXT($D3,"00/0000"),2)))*IF(VALUE(RIGHT(TEXT(D3,"00/0000"),4))< 1900,2,1))*(IF((VALUE(RIGHT(TEXT(D3,"00/0000"),4)))*IF(VALUE(RIGHT(TEXT(D3,"00/0000"),4))< 1900,2,1)< 1900,2,1)),2) & "/" & RIGHT("00" & ((VALUE(RIGHT(TEXT(D3,"00/0000"),4)))*IF(VALUE(RIGHT(TEXT(D3,"00/0000"),4))< 1900,2,1))*(IF((VALUE(RIGHT(TEXT(D3,"00/0000"),4)))*IF(VALUE(RIGHT(TEXT(D3,"00/0000"),4))< 1900,2,1)< 1900,2,1)),4)

Or you could shift to a VBA UDF. But the point here is that all this is repair work - a fixup to correct quarter data already entered as 02/2017 which excel sees as a fractional number 0.000991572. Unless you are constrained by the database format, and you indicated you are not - you should revisit your decision to enter quarter dates like that and instead get rid of the custom format "00/0000" and enter them so they are represented in excel as text ("02/2017") not as fractional numbers.

*(Fyi, I happen to be using an older version of excel at the moment. If you have the Office 365 version, there are newer built-in functions like "LET" which can allow you to shrink the forumla show here substantially)

 

[psulions83]

you are right. The text solution helped fix 80% of them. The ones that could be simplified I manually fixed them. Thank you for the help with this!