Re: [libreoffice-users] Date number has 5 digits

Hi,Even more simple:Just create a number format equal to 'nnn" "d'
(without the single quotes) and set the content of the cell to
$January.A5. The nnn is the day of the week and the d is the day in the
month in a date format.
The only thing to remember is that the "d" is valid for English-similar 
locales (my French locale equivalent is "j"). If you want to create
something that is multi-locale compatible, you will then need to use
the TEXT function with a dynamic format, something like this which will
work for both the French and English locales:
 =TEXT($January.A5,IF(TEXT(DATE(2020,1,1),"jj")="jj","nnn"" ""d","nnn""
""j")). The format can be calculated on another sheet (or in a hidden
cell) and referred to using a cell name, which would give a formula
like this:  =TEXT($January.A5,DATE_FORMAT) if the name of the cell is
DATE_FORMAT.
I hope this helps.Rémy.
Le mardi 29 décembre 2020 à 16:34 +0100, Rob Jasper a écrit :
> Peter,
> Not sure what you are trying to accomplish.
> 1- The date in January.$A5 is 12/27/2021. You can see that by
> formatting cell January.$A5 as date. 
> 2- The date is internally represented as number 44557. You can see
> this by formatting January.$A5 as number.
> 3- To get the day of the month use formula =DAY(January.$A5)
> 4- Your formula generates a error because you try to do arithmetic
> with a string (“Monday “). Use ‘&' i.s.o. ‘+’ to append.      The
> formula then becomes:
> ="Monday      “&DAY($January.$A5)
> To het the weekday of the specified day use the next formula:
> =TEXT(WEEKDAY($January.A5);"NNN")&"           "&DAY($January.A5)
> Success,Rob.
>
>
>
> > Op 29 dec. 2020, om 15:27 heeft Peter Dutton <Look@iPadRing.net>
> > het volgende geschreven:
> >
> >
> > In cell         A4 is the following code;
> >
> > ="Monday               "+January.$A5
> >
> > January.$A5          has the number 27 in it (December 27)
> >
> > What         appears in cell A4 as a result is- Monday    44557
> >
> > What is         needed is- Monday    27
> >
> > I've         tried reformatting cell A4 to a date or text or a
> > number to no         success.
> >
> >
> >
> > Any         idea how to fix this?
> >
> >
> >
> > Thanks,
> >
> > Peter
> >
> > -- To unsubscribe e-mail to: 
> > users+unsubscribe@global.libreoffice.org
> > Problems? 
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: 
> > https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy