Dates altered, how to reformat correctly?

English
#1

I have a CALC file, 4 columns, 40,000+ rows;

─COLA Dup?; checks for duplicate e-mails in column B
─COLB Address: actual address XXXXXXXX@XXXX.XXX
─COLC Date Sent: entered with CTR-;
─COLD Next Send; =IF(TODAY()-Cxxx>100,TODAY()-Cxxxx)

Problem … Yesterday my whole column of ‘DATE SENT’ went from
‘7 Jun 2022’ (as originally formatted) to ‘7 Jun 18796’
COLD is calculating a 69002, 72981, 32654 … rather than a number under 365 as I want.

Tried reformatting whole column, as well as each misformatted cell → no effect
Close and reopen file → no effect
Enter CTR-; in blank cell in COLC and date is formatted correctly (7 Jun 2022)

What to do to change rest so that the dates (COLC) and formula (COLD) are correct?

Thanks for your time,
⌡im [THE BookMan]
920.265.5966

#2

This isn’t a matter of formatting. The actual values must have been changed, and how this might have been caused can’t be concluded from the crippled information you provide.
BTW: I wouldn’t expect all te cells of your column ‘DATE_SENT’ contain the same value. In what way were the values entered / pasted / calculated…?

#3

All date in the “DATE SENT” column are different … all entered by “CTRL-;”

◄ E-MAILS for LO.ods (63.9 KB)

Partial CALC file attached.

Thanks, again,
⌡im [THE BookMan]
920.265.5966

#4

Those dates have 5-digit year values, so what do you expect? If you really entered the dates by Ctrl+; shortcut then your system time went berserk. Otherwise we don’t know the procedure how the values in your document are populated. Underlying values of dates are days since a null date (standard 1899-12-30 for LibreOffice), e.g. 5152022 in C2 hence displays a date of 16005-10-02, so something may have messed around with those numeric values as well. Macros running?

Btw, as the document contains personal identifiable data (email addresses) you should had redacted it before uploading… best replace now.

#5
  1. Okt. 16005
  2. Mär. 16252
  3. Jan. 16170
  4. Nov. 18688
  5. Mär. 16252
    (copied from a sheet with German number format locale)

When you remove the number format from these values, you get
5152022
5242022
5212022
6132022
day number 5152022 formatted as date is 2. Okt. in the year 16005
A human being or some artificial intelligence may be able to guess that these integers are meant to represent dates in US American format when you insert slashes between the right digits.
5/15/2022
5/24/2022
5/21/2022
6/13/2022
5/24/2022
The following formula converts the 7 or 8 digits into a date:

=DATE(VALUE(RIGHT($C2;4));VALUE(LEFT($C2;LEN($C2)-6));VALUE(MID($C2;LEN($C2)-5;2)))
6 Likes
#6

Lol, good observation…

#7

If there is more formatting than value on a sheet, remove all formatting and things become clear.

#8

Worked like a charm ! Correct dates are now in that column.

BUT … formatted as < ‘1.22.2022 > … can’t do calculations with that formatting.
Deleted <’> in several cells and the information there is now dates (can calculate)

How to remove the rest ?
Reformatting the column didn’t help.

Thanks again for your time,
⌡im [THE BookMan]
920.265.5966

#9

Again, this has nothing to do with “formatting” by any means.
A cell has either a number or a text. In case of number, number formats do apply. In case of text, we are dealing with a sequence of characters and no number format applies.
< anyything > IS a text
< 12345 > IS a text and no number format will change that.
Replace the 3 characters <’> with menu:Edit>Find&Replace

#10

This may prove to be an error in the end.
Firstly you probably didn’t yet understand what formatting actually means.
Secondly you don’t seem to know what originally caused the issue. Any error of that kind may re-occur one day.

#11

Gave up …

Reverted to 2 day old backup.

Thanks to everyone for their efforts.

⌡im [THE BookMan]

#12

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.