Ask Your Question
0

How do i get calc to input data on a friday as long as the date is not in the future [closed]

asked 2018-04-28 12:09:04 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Libreoffice 6.0.3.2

The title says it but just in case, I'm trying to enter a figure every Friday as long as that Friday is not in the future.

This is the formula I'm using:

=IF(WEEKDAY(A65,2)=5 AND(A65=$A$3),$C$3,0)

Where column A is the Date in descending order A3 has todays date; TODAY() I want the data from C3 entered every Friday as long as it is not in the future I've played around with the AND part AND(A65<=$A$3) or AND(A65>=$A$3)

If I just use =IF(WEEKDAY(A65,2)=5 ,$C$3,0) the formula enters the correct figure every Friday but carries on into the future.

I just cant get it to work.

So as not to get confusing I wont put all variations and results from each variant as it will just confuse things.

UPDATE:

Thanks for taking the time to help, I meant no disrespect asking if you had read my original post.

I looked at your attachment but still cannot get mine to work. If I use [<=] it enters 0. If I put [>=] it enters the correct figure but carries on into the future. I've attached the sheet here so you can see yourself what's going on. As its a work in progress don't worry about the other columns and the formula in C4 is just so I can see it as im working. Only columns A and C are important at the moment. Thanks in advance.

UPDATE 2

I looked at your sheet but need time to analyse it properly.

I have found the problem and your are right in that its related to the date but what I saw looking at my dates in number format was calc putting 5 numbers in the A3 Column [43222] but the dates from A8 onward calc put 7 numbers in, always starting with 66. So the first date in A8 number format was 6643161. I checked via format cells that they were all using the same date format so I don't understand why 66 was added the the dates from A8 on.

To fix I selected all $A cells and cleared contents then entered the dates into A3 then A8 onward. Even after formatting them to the format I wanted the dates worked and looking at them in number format there is no sign of the 66 anymore.

As it is now working this bit is not important but I'm curious why 66 was added to the dates from A8 on. I know I haven't read all your info yet but there is a lot there for me to go through, take in and understand. If you or anyone knows why 66 was added to the dates from A8 on I would welcome an answer. Also note I had made sure column A was formatted as date.

Thank you for ... (more)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by MidnightMan21
close date 2018-05-03 10:59:10.644794

Comments

Calc formulas don't input anything. You are talking of formula results that will be displayed depending on format settings, probably.
See also: https://ask.libreoffice.org/en/questi...
https://ask.libreoffice.org/en/questi...
https://ask.libreoffice.org/en/questi...

Lupp gravatar imageLupp ( 2018-04-28 13:04:04 +0100 )edit

You looked at my attachment?
Well, did it work for you as expected?
If so: What might be the reasons for what a formula working in my demo doesn't work for you?
See the new update to my answer.

Lupp gravatar imageLupp ( 2018-05-01 12:51:07 +0100 )edit

Quoting @MidnightMan21: "As it is now working this bit is not important but I'm curious why 66 was added to the dates from A8 on.":
Sorry. I cannot even guess reasonably without having you original file at hand where the error first occurred.
By the way: Nobody and nothing "added 66". The two additional digits came from somehow entering / calculating the year 20088 in place of 2018.
May endure this world as long despite the terrible mistakes thos in charge of it make every day.

Lupp gravatar imageLupp ( 2018-05-02 15:19:15 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-04-28 12:56:35 +0100

Lupp gravatar image

updated 2018-05-01 13:26:50 +0100

=IF(WEEKDAY(A65;2)=5 AND(A65=$A$3);$C$3;0) tries (in an inconsequent way) to treat AND as a logical operator. It isn't. There is the function AND() with a list of parameters each introducing a logical result. In addition the comparison A65=$A$3 does not comply with your explanations. It should, most likely, read A65<=$A$3.

Thus =IF(AND(WEEKDAY(A65;2)=5;A65<=$A$3);$C$3;0) should be what you need.

(I do not understand the usage of descending order in the context.)

Edit 1 (As announced in my comment on the recent post by the OQ):
See this attached demonstration.

Edit 2 (As announced in my second comment there. Sorry, I was distracted. Thus the delivery is late.)
Two of the probable reasons for the issue:
-1- At least some of the "dates" in the OQ's sheet are actually texts instead of standard spreadsheet-dates. -2- Some specifics of the relevant formula from my demo were not correctly adapted when moving it to the OQ's sheet. Concerning -2- I cannot do anything except to advise to perform another thorough comparison. Concerning -1- I cannot offer direct help again, but I can teach a bit about the issue often striking beginners.

I tried to do this in a concise way commenting on the slightly different cases and solutions contained in this new attachment.

edit flag offensive delete link more
0

answered 2018-04-29 12:28:40 +0100

MidnightMan21 gravatar image

Thanks for the input but if you read the whole question you'll see I've already tried all variations around A65<=$A$3) and A65>=$A$3) as well as just A65=$A$3).

I've altered the date part in the question to show that i meant the date increases each day in the next row down.

edit flag offensive delete link more

Comments

1

-1- This is not an answer but a comment on my answer. Please use the tool 'add a comment' in such cases hence.
-2- Of course, I read the whole question. However, the appropriate means to choose the needed comparator may not be playing around with some.
-3- Did you read my answer completely?
-4- Did you read the three pages I gave links to in my comment on your question?
-5- To avoid wasting more time I will attach a demonstration of the solution I suggested to my answer.

Lupp gravatar imageLupp ( 2018-04-29 12:44:56 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2018-04-28 12:09:04 +0100

Seen: 87 times

Last updated: May 02 '18