[Solved] Weekday by combining cells

Discuss the spreadsheet application

[Solved] Weekday by combining cells

Postby tpox » Wed May 01, 2019 5:27 pm


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
Last edited by Hagar Delest on Wed May 01, 2019 7:24 pm, edited 1 time in total.
Reason: tagged solved
Openoffice 4.1.6 on windows 10
Posts: 2
Joined: Wed May 01, 2019 5:23 pm

Re: weekday by combining cells

Postby Lupp » Wed May 01, 2019 5:56 pm

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   Expand viewCollapse view
=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   Expand viewCollapse view
=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   Expand viewCollapse view
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   Expand viewCollapse view
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:
(15.01 KiB) Downloaded 10 times

(15.78 KiB) Downloaded 8 times
Last edited by Lupp on Wed May 01, 2019 6:51 pm, edited 3 times in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: weekday by combining cells

Postby tpox » Wed May 01, 2019 6:40 pm

that is very helpful, thankyou
Openoffice 4.1.6 on windows 10
Posts: 2
Joined: Wed May 01, 2019 5:23 pm

Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 21 guests