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
[Solved] Weekday by combining cells
[Solved] Weekday by combining cells
Last edited by Hagar Delest on Wed May 01, 2019 7:24 pm, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
Openoffice 4.1.6 on windows 10
Re: weekday by combining cells
(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 likeSurely 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 byin your case.
To get the weekday as a number in the range of 1 through 7 you can then usewhere 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 useAnd 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: and
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)
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)
To get the weekday as a number in the range of 1 through 7 you can then use
Code: Select all
=WEEKDAY(ReferenceToTheDateCell;2)
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")
See also this demo: and
Last edited by Lupp on Wed May 01, 2019 6:51 pm, edited 3 times in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München