I am trying to display a date +90 days from the result of adding another column entries from the last entry back to equal a certain number(3).
When that number is reached I need it to display the date from that row +90 days.
A Date
B number
F1 result
So basically I need to show the result as: 5/23/2017
Which is 90 days from 2/22/2017
[Solved] Display date +90 days as result of counting back
[Solved] Display date +90 days as result of counting back
Last edited by Hagar Delest on Mon Apr 24, 2017 8:24 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4.1.2 Windows 10 Home
Re: Display date +90 days as result of counting back
Greetings and welcome to the community forum!
Sorry, I'm not sure I understand what you're doing/looking for. You can easily do arithmetic on dates, as long as they are entered in a way that Calc will recognize and convert to a date value. In the USA, entering 4/22/17 is converted to the date value 42847, which means today is 42,847 days since Calc's "zero" date, 12/30/1899.
The upshot of that is that you can easily do date calculations that involve a number of days, just by adding to a date value. With today's date (value) in cell A1, ninety days from that would be A1+90. If you need to offset that by some other number of days, say in cell B1, you can include that in the formula: =A1+B1+90
Calc tries to automatically apply a date format when it thinks a formula is working with dates but you may have to explicitly apply a date format to the cell if Calc guesses wrong or if you want a custom date format.
Sorry, I'm not sure I understand what you're doing/looking for. You can easily do arithmetic on dates, as long as they are entered in a way that Calc will recognize and convert to a date value. In the USA, entering 4/22/17 is converted to the date value 42847, which means today is 42,847 days since Calc's "zero" date, 12/30/1899.
The upshot of that is that you can easily do date calculations that involve a number of days, just by adding to a date value. With today's date (value) in cell A1, ninety days from that would be A1+90. If you need to offset that by some other number of days, say in cell B1, you can include that in the formula: =A1+B1+90
Calc tries to automatically apply a date format when it thinks a formula is working with dates but you may have to explicitly apply a date format to the cell if Calc guesses wrong or if you want a custom date format.
AOO4/LO5 • Linux • Fedora 23
Re: Display date +90 days as result of counting back
Hello,
I am a pilot and we have to maintain night currency by having at least 3 takeoffs and landings within 90 days.
I am trying to show the day that that currency will expire. So I need to show the date from the latest 3rd landing
plus 90 days.
I get working with dates.
I cannot figure out the formula to sum 3 landings from the most recent entry(at the bottom) and then display that date plus 90.
I am a pilot and we have to maintain night currency by having at least 3 takeoffs and landings within 90 days.
I am trying to show the day that that currency will expire. So I need to show the date from the latest 3rd landing
plus 90 days.
I get working with dates.
I cannot figure out the formula to sum 3 landings from the most recent entry(at the bottom) and then display that date plus 90.
OpenOffice 4.1.2 Windows 10 Home
Re: Display date +90 days as result of counting back
I think I understood what the Original Questioner wanted to achieve.
I also prepared a demonstration of some solutions, but as I had to watch snooker for a while, this took some time.
I will not try to put my suggestions in words. Study the attachment, please, if interested.
I also prepared a demonstration of some solutions, but as I had to watch snooker for a while, this took some time.
I will not try to put my suggestions in words. Study the attachment, please, if interested.
- Attachments
-
- aoo88457Find_n_th_match_1.ods
- (20.91 KiB) Downloaded 89 times
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
Re: Display date +90 days as result of counting back
Lupp,
Thanks for taking a shot at this. Your solution is way above my pay grade.
The sheet I use will have additional lines often so I need to keep up with this date.
When I try to extend the formulas there is no result.
It would be preferred if I could just display my result day +90 in one cell at the top
of my spreadsheet.
Thanks for taking a shot at this. Your solution is way above my pay grade.
The sheet I use will have additional lines often so I need to keep up with this date.
When I try to extend the formulas there is no result.
It would be preferred if I could just display my result day +90 in one cell at the top
of my spreadsheet.
OpenOffice 4.1.2 Windows 10 Home
Re: Display date +90 days as result of counting back
Now I am confused.stacycfi wrote:I am a pilot and we have to maintain night currency by having at least 3 takeoffs and landings within 90 days.
I am trying to show the day that that currency will expire. So I need to show the date from the latest 3rd landing...
"at least 3 takeoffs and landings": '3 TakeOffs and 3 LanDings'? 3 of any like 'TO-LD-TO' or 'LD-TO-LD'? Where are these events listed?
I also couldn't get the term "night currency" for sure.
Can there be more than one events to count on the same date - or do you service a mode where 'TO' and 'LD' are assured to be the same date? Does an entry of 1 mean a completed flight? (I wonder for what reason you marked days with an event by a number instead of an abbreviation like "x" or "TO" or whatever.)
"...the latest 3rd landing..." I do not understand this phrase at all.
This will be simplest variant as soon as I understood clearly what you want to achieve. Until now I assumed it was a "moving date" you wanted: Starting with an arbitrary given date and looking for the "third event" from that date forward. (The addition of 90 to an interim result I won't mention any more because it's trivial.)stacycfi wrote:It would be preferred if I could just display my result day +90 in one cell at the top
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
Re: Display date +90 days as result of counting back
Sorry to confuse,
I was just trying to explain what I wanted to accomplish by explaining what this is for.
I don't make entries daily. But I do add a row for every flight. So there can be more than 1 event per day.
So (column A) is the date (column B) is a number, this could be blank or have a number 1 thru 4.
I start from the most recent entry(the last row) add each entry until 3 and show the date (column A) equal to 3 or more (column B).
I have learned how to do all other formulas on my own except this.
Thank you for your help.
I was just trying to explain what I wanted to accomplish by explaining what this is for.
I don't make entries daily. But I do add a row for every flight. So there can be more than 1 event per day.
So (column A) is the date (column B) is a number, this could be blank or have a number 1 thru 4.
I start from the most recent entry(the last row) add each entry until 3 and show the date (column A) equal to 3 or more (column B).
I have learned how to do all other formulas on my own except this.
Thank you for your help.
OpenOffice 4.1.2 Windows 10 Home
Re: Display date +90 days as result of counting back
Here's one approach. It depends on formulas in a helper column, which means you'll have to manually copy the formulas (column C) to new rows.
- Attachments
-
- 90 days updated_acknak.ods
- (11.68 KiB) Downloaded 140 times
AOO4/LO5 • Linux • Fedora 23
Re: Display date +90 days as result of counting back
acknak,
That was perfect. I did not know columns were summed that way.
This is exactly what I wanted. And simple. Perfect.
Thanks much.
That was perfect. I did not know columns were summed that way.
This is exactly what I wanted. And simple. Perfect.
Thanks much.
OpenOffice 4.1.2 Windows 10 Home