Date number has 5 digits

classic Classic list List threaded Threaded
7 messages Options
pda123 pda123
Reply | Threaded
Open this post in threaded view
|

Date number has 5 digits



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: [hidden email]
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
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Date number has 5 digits

Put that number in E1 and formated it to a date field.
Comes up as December 27, 2021

Used this formula
="Monday "&TEXT(DAY(E1),"##")

Using a + I get an error since you are adding a number to a string?
="Monday "&E1 seems to give the number value of the date.




On 29 Dec 2020 at 9:27, Peter Dutton wrote:

To:             [hidden email]
From:           Peter Dutton <[hidden email]>
Subject:         [libreoffice-users] Date number has 5 digits
Date sent:       Tue, 29 Dec 2020 09:27:20 -0500

>
>
> 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: [hidden email]
> 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Date number has 5 digits

In reply to this post by pda123
At 09:27 29/12/2020 -0500, Peter Dutton wrote:
>In cell A4 is the following code;
>="Monday"+January.$A5

Do you mean that plus sign - or perhaps an ampersand?

>January.$A5 has the number 27 in it (December 27).

I'm not sure what you think you mean by adding "(December 27)"! What
I suspect you have in that cell is not the number 27 but instead the
date 27 December 2021 (which indeed will be a Monday) - but formatted
so as to display only the numerical day part of that date.

>What appears in cell A4 as a result is- Monday 44557.

Good. You are concatenating the text "Monday" with the value of that
date, and in this context no regard is had to the formatting of the
cell. The actual value in your cell for 27 December 2021 is 44557,
which is the number of days from the start date until that day.

>What is needed is- Monday 27
>I've tried reformatting cell A4 to a date or text or a number to no success.

Reformatting a cell already containing a value does not change the
value - merely the way it is displayed. Your cell still contains the
number 44557.

>Any idea how to fix this?

You need to extract the day number part of the date value before you
concatenate it with the text "Monday". To do that, use
="Monday "&DAY(January.$A5)

Depending on exactly what you need, have you considered using
=TEXT(January.$A5;"NNNND")
instead?

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Rob Rob
Reply | Threaded
Open this post in threaded view
|

Re: Date number has 5 digits

In reply to this post by pda123
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 <[hidden email]> 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: [hidden email]
> 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: [hidden email]
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
remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
> > 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:
> > [hidden email]
> > 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: [hidden email]
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
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Date number has 5 digits

In reply to this post by pda123
Den tis 29 dec. 2020 kl 15:29 skrev Peter Dutton <[hidden email]>:

>
>
> In cell
>           A4 is the following code;
>
>
> ="Monday
>           "+January.$A5
>

Okay, that would give #VALUE! as a result. With you so far.


>
> January.$A5
>           has the number 27 in it (December 27)
>

December 27 gives me the value 44192, not 27. 27 december 2020 is 44192
dags from 1899-12-30, so 44192 makes way more sense than 27 (which would
rather be 26 january 1900), but let's go on.


>
>
> What
>           appears in cell A4 as a result is- Monday    44557
>

No, it's #VALUE! I tested it a few seconds ago.

>
>
> What is
>           needed is- Monday    27
>
>
> I've
>           tried reformatting cell A4 to a date or text or a number to no
>           success.
>

If you want 27 December to display as Monday 27, then just format the cell
that way.

1. Enter the date you want to format in a cell. Use the format that
corresponds to your language settings. In my case I would type:
20-12-27
or
2020-12-27
Now use the following as your cell's number format:
DDDD D

Result (in my case):
söndag 27
 If I set the language to US English, it will display:
Sunday 27

Why not Monday, you might ask? Because 27 December 2020 was a Sunday.


>
>
>
> Any
>           idea how to fix this?
>

If you want 2020-12-27 to be a Monday, then no. Otherwise, see above.



Kind regards

Johnny Rosenberg


--
To unsubscribe e-mail to: [hidden email]
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
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Date number has 5 digits

Den tors 31 dec. 2020 kl 10:43 skrev Johnny Rosenberg <
[hidden email]>:

>
>
> Den tis 29 dec. 2020 kl 15:29 skrev Peter Dutton <[hidden email]>:
>
>>
>>
>> In cell
>>           A4 is the following code;
>>
>>
>> ="Monday
>>           "+January.$A5
>>
>
> Okay, that would give #VALUE! as a result. With you so far.
>
>
>>
>> January.$A5
>>           has the number 27 in it (December 27)
>>
>
> December 27 gives me the value 44192, not 27. 27 december 2020 is 44192
> dags from 1899-12-30, so 44192 makes way more sense than 27 (which would
> rather be 26 january 1900), but let's go on.
>

Days, not ”dags”. Day=dag in my language, sorry for the confusion…


Kind regards

Johnny Rosenberg

>
>
>>
>>
>> What
>>           appears in cell A4 as a result is- Monday    44557
>>
>
> No, it's #VALUE! I tested it a few seconds ago.
>
>>
>>
>> What is
>>           needed is- Monday    27
>>
>>
>> I've
>>           tried reformatting cell A4 to a date or text or a number to no
>>           success.
>>
>
> If you want 27 December to display as Monday 27, then just format the cell
> that way.
>
> 1. Enter the date you want to format in a cell. Use the format that
> corresponds to your language settings. In my case I would type:
> 20-12-27
> or
> 2020-12-27
> Now use the following as your cell's number format:
> DDDD D
>
> Result (in my case):
> söndag 27
>  If I set the language to US English, it will display:
> Sunday 27
>
> Why not Monday, you might ask? Because 27 December 2020 was a Sunday.
>
>
>>
>>
>>
>> Any
>>           idea how to fix this?
>>
>
> If you want 2020-12-27 to be a Monday, then no. Otherwise, see above.
>
>
>
> Kind regards
>
> Johnny Rosenberg
>
>
>>
>>
>>
>>
>> Thanks,
>>
>>
>> Peter
>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> 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: [hidden email]
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