## [Solved] Formulas for returning desired date

Discuss the spreadsheet application

### [Solved] Formulas for returning desired date

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

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

Villeroy
Volunteer

Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formulas for returning desired date

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

=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

Villeroy
Volunteer

Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formulas for returning desired date

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