In Excel 2007, how should I change formatting so I don't get "2E+07" but instead the date written as "20170620"?
I have this formula in the cell:
='Loppöversikt och loppanalyser'!C2
And it should have retrieved the date "20170620" from C2 on that tab. But instead, "2E+07" is written in the cell. I've tried to change the formatting in the cell, but nothing is accepted by Excel.
='Loppöversikt och loppanalyser'!C2
And it should have retrieved the date "20170620" from C2 on that tab. But instead, "2E+07" is written in the cell. I've tried to change the formatting in the cell, but nothing is accepted by Excel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. 00000000 solved it.
You'll have to use custom formatting for that. Such as:
Code Description Example (January 1, 2005)
m Month number without a leading zero 1
mm Month number with a leading zero 01
mmm Month name, short form Jan
mmmm Month name, full form January
mmmmm Month as the first letter J (stands for January, June and July)
d Day number without a leading zero 1
dd Day number with a leading zero 01
ddd Day of the week, short form Mon
dddd Day of the week, full form Monday
yy Year (last 2 digits) 05
yyyy Year (4 digits) 2005
When setting up a custom time format in Excel, you can use the following codes.
Code Description Displays as
h Hours without a leading zero 0-23
hh Hours with a leading zero 00-23
m Minutes without a leading zero 0-59
mm Minutes with a leading zero 00-59
s Seconds without a leading zero 0-59
ss Seconds with a leading zero 00-59
AM/PM Periods of the day
(if omitted, 24-hour time format is used) AM or P
For other number formats:
https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/#custom-date-format
https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/#decimal-places
Code Description Example (January 1, 2005)
m Month number without a leading zero 1
mm Month number with a leading zero 01
mmm Month name, short form Jan
mmmm Month name, full form January
mmmmm Month as the first letter J (stands for January, June and July)
d Day number without a leading zero 1
dd Day number with a leading zero 01
ddd Day of the week, short form Mon
dddd Day of the week, full form Monday
yy Year (last 2 digits) 05
yyyy Year (4 digits) 2005
When setting up a custom time format in Excel, you can use the following codes.
Code Description Displays as
h Hours without a leading zero 0-23
hh Hours with a leading zero 00-23
m Minutes without a leading zero 0-59
mm Minutes with a leading zero 00-59
s Seconds without a leading zero 0-59
ss Seconds with a leading zero 00-59
AM/PM Periods of the day
(if omitted, 24-hour time format is used) AM or P
For other number formats:
https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/#custom-date-format
https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/#decimal-places
The native and internal date is a float with number of days since (*can't remember, 1.1.1900 or so), and the fractional part of the day (12:00 noon is 0.5).