[Solved] Weekday by combining cells

[tpox]

hi

i need to get openoffice calc to tell me the weekday by combining the data from 3 other cells...ie

cell 1 would have the month 'December'
cell 2 would have the day '1'
cell 3 would have the year '2019'


i am hoping for 'Sunday'

i have played with the functions but cant seem to be able to get it to give me the correct result from other cells. can you help me please. thanks

[Lupp]

(Editing:)
Sorry. I made the demo with LibreOffice where the DATEVALUE() function works the way I used it. Now I also tested with Apache OpenOffice V 4.1.5 and had to notice that it didn't work. If I find the time I will rework the example.
Once again: The way to give dates described by the original questioner is very inexpedient.
(End Edit)
Concerning the above: Well, I found that AOO insists on the space as delimiter and on the year given as the last part in the text describing the complete date. Since this is against reason I will no longer try to give a solution by DATEVALUE() for AOO. If you have a sense for bad jokes you may use something like

Code: Select all

=DATEVALUE(B13&" "&MATCH(B14;{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"};0)&" "&B15)

Surely better is to lookup the ordinal numbers for month-names, and then use the DATE() function.



If your three cells actually contain text as you possibly want to hint by the apostrophes, that's bad, at least concerning the month. Why don't you use the ordinary unambiguous date format YYYY-MM-DD in one cell? Spreadsheets have every function to treat dates represented as numbers (and shown in a date format then mostly).

If your cells contain texts, you need to create a numeric date representation in a first step. Based on names for months this depends on the UI-language or the locale.
Assuming your settings are based on English, you can get the numeric date by

Code: Select all

=DATEVALUE(cell3 & "-" & cell1 & "-" & cell2)

in your case.
To get the weekday as a number in the range of 1 through 7 you can then use

Code: Select all

=WEEKDAY(ReferenceToTheDateCell;2)

where the number 2 selects the variant regarding the international standard with 1 for monday and 7 for sunday.
If you have no need of any calculations with the result, and want to get the name of the weekday, you should use

Code: Select all

=TEXT(ReferenceToTheDateCell;"DDDD")

And if it's only about seeing that name, simply apply the format code YYYY to the cell containing the numeric date.

See also this demo:

aoo97880DatesAgain_1.ods

(15.01 KiB) Downloaded 91 times

and

aoo97880DatesAgain_2.ods

(15.78 KiB) Downloaded 81 times

[tpox]

that is very helpful, thankyou