Date number has 5 digits

users
#1

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

#2

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.

#3

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

#4

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.

#5

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.

#6

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

#7

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