Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 31 Aug 2020 16:05:21 +0200Calc Fill Down Weekdayhttps://ask.libreoffice.org/en/question/263313/calc-fill-down-weekday/ I need to fill down a column with weekdays (I do this each month).
Mon
Tue
Wed
ect;
For the life of me I can't seem to get it right.dta116Mon, 31 Aug 2020 16:05:21 +0200https://ask.libreoffice.org/en/question/263313/COUNTIFS to get weekdays split in am/pm counthttps://ask.libreoffice.org/en/question/260025/countifs-to-get-weekdays-split-in-ampm-count/ Hi,
I'm trying to count the occurrence of every weekday split into am and pm.
![Screenshot](/upfiles/1597327411385913.png)
Just summing the weekdays works fine with =SUMPRODUCT(--(WEEKDAY(A1:A100,2)=D4)) and the help column.
But I'm not able to figure out why F4 doesn't work to get the Monday AM count.
=COUNTIFS(--(WEEKDAY($A$1:$A$100,2)),=D4,(HOUR($A$1:$A$100)<12))
Can somebody help me please?BloodAngelScriptorThu, 13 Aug 2020 16:10:57 +0200https://ask.libreoffice.org/en/question/260025/LibreOffice Calc WEEKDAY() functionhttps://ask.libreoffice.org/en/question/244900/libreoffice-calc-weekday-function/On a spreadsheet I have a cell where the user inputs a period ending date which should always fall on a Sunday. The format for that cell is set for Date, 31 Dec 1999. The validation rule for that cell:
Allow: Date
Data: Equal
Value: WEEKDAY(J4,1) = 1 (the 2nd parameter is correct for my locale)
No matter what date is entered, the input is considered invalid. This is working just fine with Excel. I have no idea what's going on. Anyone?
Cheers!
Image in response to Mike's reply:
![image description](/upfiles/15897829102747579.png)MJ12Mon, 18 May 2020 03:34:17 +0200https://ask.libreoffice.org/en/question/244900/How do I update the date for each weekday when the weekdays are in fixed columns?https://ask.libreoffice.org/en/question/241511/how-do-i-update-the-date-for-each-weekday-when-the-weekdays-are-in-fixed-columns/I have a fixed set of seven columns for each day of the week...Monday through Sunday. I need to automatically update the current date for each day of the week. Using the Today() function and adding seven doesn't work in this case because the days of the week are fixed for each column, kind of like a planner week. Does anybody know how to do this easily? I have the feeling I am overlooking something simple.janthirdThu, 30 Apr 2020 01:21:15 +0200https://ask.libreoffice.org/en/question/241511/WEEKDAY value and name don't matchhttps://ask.libreoffice.org/en/question/220937/weekday-value-and-name-dont-match/I'm making a table with dates in one column and weekdays in another in LibreOffice Calc. The column `$A` has dates formatted as YYYY-MM-DD, the column `$B` has weekdays derived from these dates using `WEEKDAY` formula and is formatted as NN (shortened weekday name).
Now, I have locale set to Russian and our week starts on Monday. The issue is that while the displayed weekday name matches the actual weekday of the corresponding date, the weekday cell `VALUE` is off by 1 for my locale (i.e. Friday has value of 6 instead of 5).
According to documentation for `WEEKDAY` function (https://help.libreoffice.org/6.1/en-US/text/scalc/01/func_weekday.html), it has the following syntax: WEEKDAY(Number; Type). The default type is 1 and the default returned values start from 1 for Sunday, 2 for Monday etc. But I can override this by setting type parameter to `2`, so Monday becomes the first day of the week. And that's where strange things start to happen.
For the cell `$A2` set to `2019-11-01` (which is Friday), the cell `$B2` results in the following:
If the formula is `=WEEKDAY($A2)`, the displayed weekday is `Friday` (correct), but the cell VALUE is `6` (incorrect).
If the formula is `=WEEKDAY($A2;2)`, the cell `VALUE` becomes `5` (correct), but the displayed weekday becomes Thursday` which is wrong!
What am I doing wrong? Or what settings should I change to get weekday number and weekday name in sync according to my locale? I was under impression that this should be happening automatically, but, apparently, it doesn't.lfWed, 11 Dec 2019 13:41:19 +0100https://ask.libreoffice.org/en/question/220937/Extract(Weekday FROM "date") not working in Libreoffice Base, Firebird embbededhttps://ask.libreoffice.org/en/question/196278/extractweekday-from-date-not-working-in-libreoffice-base-firebird-embbeded/Hi, I can't get return in a query with the extrac() function in LibreOffice Base, Firebird Embedded database.
SELECT
EXTRACT( WEEKDAY FROM "Date" ) "DayOfWeek",
EXTRACT( DAY FROM "Date" ) "Day",
EXTRACT( MONTH FROM "Date" ) "Month"
FROM
"Tbl_Schedule"
This function returned error:
EXTRACT( WEEKDAY FROM "Date" )
However, DAY and MONTH returned correct answers.
I am currently using
Version: 6.1.6.3 (x64)
Can anyone help? What are the alternatives?
ThanksesphiTue, 04 Jun 2019 19:14:21 +0200https://ask.libreoffice.org/en/question/196278/Calc: AutoFilter on WEEKDAY columnhttps://ask.libreoffice.org/en/question/196193/calc-autofilter-on-weekday-column/I have a column that is populated by the `WEEKDAY` function, pointing to the date column next to it, so as to give the day-of-the-week that an event occurred.
I have applied AutoFilter to the sheet, but the drop-down filter value list does not populate correctly for this column - the only value available to select is "1900 > January"
![image description](/upfiles/15596400288293332.png)
Is there a workaround for this?sharyl93Tue, 04 Jun 2019 10:25:27 +0200https://ask.libreoffice.org/en/question/196193/[SOLVED] Only average if cells contain a certain wordhttps://ask.libreoffice.org/en/question/185512/solved-only-average-if-cells-contain-a-certain-word/ Hello!
In this example I'd like F7 to calculate the average time of the range D5:D29 only if the cell left to it contains the word "Monday" i. e. C7 shall average all times on mondays.
How do I go about solving this?
![image description](/upfiles/15517744112668775.png)LaniakeaTue, 05 Mar 2019 09:31:57 +0100https://ask.libreoffice.org/en/question/185512/Averageif and Weekdayhttps://ask.libreoffice.org/en/question/185031/averageif-and-weekday/ Good morning,
In calc, I have a couple of columns - one with dates and the other with values. What I want to do is to average the values where the date column equates to a weekend and similarly for a weekday.
So something like AVERAGEIF($A$1:$A$10,WEEKDAY(?,2)>5,$B$1:$B$10)
...but how can I get WEEKDAY to work on the cell from the range? Or is there a better way of going about it?
ThankssteveTuFri, 01 Mar 2019 09:29:13 +0100https://ask.libreoffice.org/en/question/185031/Sum up based on date in the same rowhttps://ask.libreoffice.org/en/question/127492/sum-up-based-on-date-in-the-same-row/Hi there,
I'd like to sum up a column if and only if other column say so. I thought it could be done with SUMIF. My problem is that I have a sheet with dates on column A and values on column B and I am trying to do weekday report of progress...
My first try was something like this: "=SUMIF(B1:B100,WEEKDAY(A1)=3)". It is obivious wrong because it sums up all elements or it does nothing. I'd like when I try to sum up B2 I test B1 and not A1. Sum up B3, test A3 and so on.rrluccaMon, 28 Aug 2017 13:00:20 +0200https://ask.libreoffice.org/en/question/127492/Working formula in OpenOffice Calc does not work in LibreOffice Calchttps://ask.libreoffice.org/en/question/45100/working-formula-in-openoffice-calc-does-not-work-in-libreoffice-calc/Hi ,
I have a formula in Openoffice Calc that is not working in LibreOffice Calc... can anyone help me make it work?
It is a simple formula to calculate the day of the week from a date:
TEXT(WEEKDAY(DATEVALUE(TEXT(D39&"-"&$Data.K32&"-"&resultsYear,1))),"ddd")
where resultsYear is a RANGE which has a value '2014'.
Data.K32 has the value 'Mar'
D39 is '28'
There appears an Error:502 in the cell.....
OpenOffice gives the correct value of 'Sat'
Thanks in anticipation,
Allan
Anyone?ThompaSun, 18 Jan 2015 04:50:56 +0100https://ask.libreoffice.org/en/question/45100/