[Solved] Formulas for returning desired date

Discuss the spreadsheet application

[Solved] Formulas for returning desired date

Postby jstack » Wed Jan 08, 2020 12:56 pm

Assume I have the date 4/22/20 in cell A1. I want to use an IF formula in A2 to look at the date in A1 and IF the date is greater than a specific date of the month (example: MM/08/YY), then equal A1 plus 1 month. Otherwise equal A1. I tried the following:
=IF(A1>MM/08/YY;DATE(YEAR(A1);MONTH(A1)+1;DAY(A1));A1)
The above formula did not work. The month and year are irrelevant, that's why I used MM and YY rather than actual.
Last edited by jstack on Wed Jan 08, 2020 5:14 pm, edited 1 time in total.
OpenOffice 4.1.2 on Windows 7
jstack
 
Posts: 11
Joined: Wed Jun 13, 2018 10:28 pm

Re: Formulas for returning desired date

Postby Villeroy » Wed Jan 08, 2020 2:43 pm

Of course it does not work because MM/08/YY is wild guessing. The solution can be found in the other part of the formula where you calculate next month's date correctly using the DATE function.
May be you want something like:
=IF(DAY(A1)>8;DATE(YEAR(A1);MONTH(A1)+1;DAY(A1));A1)
or
=IF(A1>DATE(YEAR(A1);MONTH(A1);8);DATE(YEAR(A1);MONTH(A1)+1;DAY(A1));A1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formulas for returning desired date

Postby jstack » Wed Jan 08, 2020 3:23 pm

Thank you Villeroy, that worked perfectly. I'm also having trouble with another similar issue. Is there a way to look at the date in A1 and return the next Friday date following the date in A1? Thank you!
OpenOffice 4.1.2 on Windows 7
jstack
 
Posts: 11
Joined: Wed Jun 13, 2018 10:28 pm

Re: Formulas for returning desired date

Postby Villeroy » Wed Jan 08, 2020 4:23 pm

=WEEKDAY(A1) returns a weekday number between 1 (Sunday) and 7 (Saturday).
=A1-WEEKDAY(A1) returns the Saturday before A1.
=A1-WEEKDAY(A1)+6 returns the next Friday after that Saturday. In case of a Friday it returns the same Friday.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formulas for returning desired date

Postby John_Ha » Wed Jan 08, 2020 5:11 pm

See Calc Functions listed by category for definitions of all functions and examples of their use or check the Calc Guide.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7825
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 23 guests