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]

Why not input as dates?

 

[psulions83]

that is probably how it should have been to begin. When I format to that, the date goes to 01/1900

 

[rlv01]

that is probably how it should have been to begin. When I format to that, the date goes to 01/1900

01/1900 would be the result if you formatted the number 0.000991571641051066 as "mm/yyyy" . But why would you do that? The raw number for an excel date of 02/2017 is 42767, so if you format the cell as "mm/yyyy" then input your data as per your "02/2017" example, all should be well. Or are you talking about converting those "00/0000" values back to dates?

 

[psulions83]

01/1900 would be the result if you formatted the number 0.000991571641051066 as "mm/yyyy" . But why would you do that? The raw number for an excel date of 02/2017 is 42767, so if you format the cell as "mm/yyyy" then input your data as per your "02/2017" example, all should be well. Or are you talking about converting those "00/0000" values back to dates?

converting them back to dates. Excel looks like it is actually doing 02/2017 as that equals what is in the cell at 0.00991571641051066. They should be dates but I think the formatting was setup wrong and this is a result of that

 

[psulions83]

it is actually not MM/YYYY it is a quarter value. QQ/YYYY

 

[rlv01]

So what data type is the database expecting to see for QQ/YYYY when you upload? String?

 

[psulions83]

Yes. That format is correct. Or QQYYYY

 

[rlv01]

Ok, so there are a few ways to approach this. One is to convert your data to text, which the database import will presumably like.

Example:

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	2017	0.000495786	01/2017	<= Double	01/2017	<= 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	5	2017	0.002478929	05/2017	<= Double	05/2017	<= String
8	6	2017	0.002974715	06/2017	<= Double	06/2017	<= String
9	7	2017	0.003470501	07/2017	<= Double	07/2017	<= String
10	8	2017	0.003966287	08/2017	<= Double	08/2017	<= String
11	9	2017	0.004462072	09/2017	<= Double	09/2017	<= String
12	10	2017	0.004957858	10/2017	<= Double	10/2017	<= String
13	11	2017	0.005453644	11/2017	<= Double	11/2017	<= String
14	12	2017	0.00594943	12/2017	<= Double	12/2017	<= String
Sheet1 (2)

Cell Formulas
Range	Formula
C3	C3	=A3/B3
D3:D14	D3	=C3
F3:F14	F3	=TEXT($C3,"00/0000")

 

[psulions83]

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