Forum: Author Hangout

Check 1900 again and you will find that it is a leap year (i.e. divisible by 4 with no remainder).

Nope, Century years are only leap years if they are divisible by 400.

https://en.wikipedia.org/wiki/Century_leap_year

It has only happened a few times. 1700, 1800, 1900. Next one is 2100.

None of those are leap years. The next century leap year will be 2400.

I was listing the century dates that WERE NOT leap years.

Yes, that is what you did, but the proceeding text does not make sense in reply to my comment if that was your intent.

Me:

Nope, Century years are only leap years if they are divisible by 400.

It has only happened a few times.

The correction from the old calendar happened in 1582.

Different countries adopted the calendar in different years/centuries, so you can't give a single date to when the correction occured (without adding country info).

Unless you are suggesting that there are countries that didn't adopt the correction until after 1600, it's not really relevant to the discussion at hand.

If it came across correctly as a date with a general format, you should see a big integer like 44173 because that's how Excel stores dates.

It's only when I modify it (and keep it as "general") that it converts it to the large integer. That is what's confusing me.

It's MS Excel. Confusing users is a major goal. :)

because Excel's default date format is MM/DD/YYYY

No it's not, it defaults to Windows culture settings.

It took the decision to replicate the Lotus bug for data compatibility.

But with Lotus long gone and hundreds of users complaining about it, they still refuse to fix it.

I'd be interested to know whether OOCalc has the same 'facility'.

I just tested OO by entering 2/1/1900 and streatching it out to an auto fill series, it correctly goes from 2/28/1900 to 3/1/1900 with no 2/29/1900.

It may not apply to Excel

That doesn't apply to either Excel or OO Calc. They will recognize dates entered in any supported format and automatically convert them under the hood.

Yes, it is a spreadsheet.

What format specifically? .xls, .xlsx, .odx, something else?

Note: Excel will open .cvs files as if they were a spread sheet, without doing an import, but it's not actually a spreadsheet.

.cvs (comma separated values) is just a text file with comma delimited values.

If what you are getting from KDP is a .cvs file, there's your problem. It's all just text data until you do something that makes Excel actually process the value in a particular field.

When I check the formatting of the field in the generated spreadsheet it is "general" and it does not have the ' in front of the date.

From the link I put in my first answer:

An apostrophe (') before you enter a number, such as '11-53 or '1/47. The apostrophe isn't displayed in the cell after you press Enter.

(I added bold)

The row you copy from doesn't have the ', maybe because of this. Your fields shouldn't either after you put in an apostrophe, then the date , and press Enter.

But the ' stays in the field after hitting Enter or clicking on the check sign.

If you are seeing the ' what's in the cell is already text data.

When you change the date, are you editing the string in the field or entering a whole new date from scratch.

If you enter a date from scratch, Excel will automatically convert it and set the cell format to a date type.

That's what I don't understand. It's treating it as if the format is "text" but it says it's "general".

Read 'general' more like 'whatever I can make of it'. 'text' is what it says, just text.

@Switch Blayde

That's what I don't understand. It's treating it as if the format is "text" but it says it's "general".

Read 'general' more like 'whatever I can make of it'. 'text' is what it says, just text.

Once 2020-12-08 is actually in the file for a given cell location, Excel won't know how to handle it as anything other than text. A date should have an integer in the raw data for the cell.

If KDP is somehow building the .xlsx without using Excel or converting from a .CVS that's something that can happen.

Once 2020-12-08 is actually in the file for a given cell location, Excel won't know how to handle it as anything other than text. A date should have an integer in the raw data for the cell.

Not if you change the text, it's just interpreted again.

And it displays fine until I change the data.

At which point, Excel goes "hey, this looks like a date" and does a whole bunch of stuff for you under the hood.

That's what I don't understand. It's treating it as if the format is "text" but it says it's "general".

IIRC, prepending the apostrophe will override the default format for the cell and cause the cell to be displayed as justified text.
Also, unless the exact date format identical to what was used in the import is your default date format, I don't think date format will be used - but when you enter any date data into a text field excel assumes you want to use its date formatting.
Nobody ever claimed M$ wrote decent code. Or logically internally consistent, for that matter.

Also, unless the exact date format identical to what was used in the import is your default date format

Not quite.

1. In doing an actual import if you declare a column as date, you can select the format to use, it is not limited to the default date format.

2. Excel will open a .csv file without doing an import. Anything that isn't strictly numeric will be treated as text.

I still can't figure out why the format of the cell I get from KDP is "general" and not "text" when it's operating like "text" (a date without the apostrophe).

I'm not sure how KDP is generating the .xlsx file.

They may be generating a .csv, then converting it to .xlsx.

But in general, if the cell contains any characters other than +-=.1234567890 Excel and any other spreadsheet will treat the data as text, with or without the ' unless the text matches a defined date format.

= as the first character in the cell will make it interpret the cell value as a formula

+ or - would have to be at the start of a string of digits. Without either = or ' 2010-03-25 can only be text or a date. And the value of a cell in the actual file for a date would be an integer and Excel converts the integer to a date when it renders the cell value.

This is why I suspect that they first created a CSV and later used Excel or some other tool to convert it to a .xlsx. When Excel loads a CSV, it will treat everything that isn't a straight number as text.

It's the only path I can think of to get 2020-12-08 into a cell in the file itself.