Show days left until reset based on date and condition associated with date

Question

Level 1

5 points

Show days left until reset based on date and condition associated with date

My question is simlilarly based to the previous discussion https://discussions.apple.com/thread/3038287

What I need to do is display the number of days until I have to reset a flight condition (FS Column) based on a set number of days (60 days). I have several colums of information but the two I would use is a Date Column and the FS Column(drop down menu with text for options). I would need to be able to look at certain FS options and use the date associated with it to subtract from 60.

If FS= "NG" use date associated with it to add 60 days, and then use that date to compare it with today's date to show how many days are left till the 60 day mark.

So if the last "NG" was Jan 1, 2011 and today was Feb 1, 2011 I would want one cell to show 30 days and another cell to show Mar 2, 2011.

I need to fly in a certain condition once every 60 days. I want to show how many days left I have until I need to reset that 60 day counter. Also, I would like to have another cell show the date associated with the day I would need to reset that counter on.

Unfortunately I am in a limited internet environment so I can not post a picture of my spreadsheet. Sorry for the over explination, trying to make it as clear as possible first go round.

Thanks in advance!

Posted on Oct 10, 2011 10:41 PM

Reply

Answer 1

Oct 11, 2011 2:28 AM in response to Aronvorn

Aronvorn,

It's not necessary for you to post an image for us to see your table. Just copy the table and paste it into your reply here. I don't think that would take much bandwidth.

Jerry

Reply

Answer 2

Aronvorn Author

Level 1

5 points

Oct 11, 2011 3:46 AM in response to Jerrold Green1

Hey Jerry,

Date	DS	FS	Hrs	Seat	Mission ID
1-Aug-11	PI	D	1.0	F	C
1-Aug-11	PI	N	0.5	F	C
2-Aug-11	PI	D	0.5	F	C
2-Aug-11	PI	NS	1.4	F	C
4-Aug-11	PI	NS	0.2	F	C
12-Aug-11	PI	NG	1.0	F	C
12-Aug-11	PI	NS	1.2	F	C
14-Aug-11	PI	NG	0.5	F	C
14-Aug-11	PI	H	0.5	F	C
14-Aug-11	PI	NS	0.6	F	C
20-Aug-11	PI	NS	2.1	F	C
21-Aug-11	PI	NG	2.0	F	C
22-Aug-11	PI	NG	1.0	F	C
22-Aug-11	PI	NS	0.4	F	C
29-Aug-11	PI	NS	0.5	F	C

Here is a snipit of my table. What I would like to do is have one cell show a number of days and another cell show the date. The formula would need to find the last specific FS condition (say NG or NS) and look at the date associated with it. Then add 60 days to that date. That date would be the second cell I want. Then the days cell I want is the number of days from today to that date. So in this case, 60 days from 29 Aug 11 would be 28 Oct 11 and the number of days from today the 11th would show 17 days.

Thanks for the tip on copy and pasting the table. By limited internet capabilities I was eluding to being deployed with really really bad civilian internet for my mac to connect to (like 7 kbps on good days). And its taken me 40 mins to log in and get this posted. Thanks again for the help! Finally have the Numbers and Formulas/Functions guides downloaded, that was an over night guy haha.

Reply

Answer 3

Oct 11, 2011 5:26 AM in response to Aronvorn

Aron,

I added a few columns to correlate the conditions and dates. There may be a better way than this brute force approach, but this will work.

Date	DS	FS	Hrs	Seat	Mission ID	D	H	N	NG	NS
Aug 1, 2011	PI	D	1.0	F	C	8/1/11
Aug 1, 2011	PI	N	0.5	F	C	8/1/11
Aug 2, 2011	PI	D	0.5	F	C	8/2/11
Aug 2, 2011	PI	NS	1.4	F	C	8/2/11
Aug 4, 2011	PI	NS	0.2	F	C	8/4/11
Aug 12, 2011	PI	NG	1.0	F	C	8/12/11
Aug 12, 2011	PI	NS	1.2	F	C	8/12/11
Aug 14, 2011	PI	NG	0.5	F	C	8/14/11
Aug 14, 2011	PI	H	0.5	F	C	8/14/11
Aug 14, 2011	PI	NS	0.6	F	C	8/14/11
Aug 20, 2011	PI	NS	2.1	F	C	8/20/11
Aug 21, 2011	PI	NG	2.0	F	C	8/21/11
Aug 22, 2011	PI	NG	1.0	F	C	8/22/11
Aug 22, 2011	PI	NS	0.4	F	C	8/22/11
Aug 29, 2011	PI	NS	0.5	F	C	8/29/11

Then I added a summary table:

FS Condition	LastDate	Plus 60 Days	Today is Oct 11, 2011	Alarm
D	8/2/11	10/1/11	10d	Overdue
H	8/14/11	10/13/11	-2d	Due in 10 days or less
N	8/1/11	9/30/11	11d	Overdue
NG	8/22/11	10/21/11	-10d
NS	8/29/11	10/28/11	-17d

You can color-code the Alarms. I did, but the colors didn't copy to the posting.

In the first table...

The column for condition D: =IF($C=G$1, $A, "")

The column for condition H: =IF($C=H$1, $A, ""), etc.

In the summary table...

Last Date for Row 2: =MAX(Table 1 :: G)

Last Date for Row 3: =MAX(Table 1 :: H), etc.

In the Plus 60 days column: =B+60

In the "Today is"... column: =TODAY()-C

In the Alarm column: =IF(C<TODAY(), "Overdue", IF(C<(TODAY()+10), "Due in 10 days or less", ""))

Is this close?

Jerry

Reply

Answer 4

Aronvorn Author

Level 1

5 points

Oct 11, 2011 9:11 PM in response to Jerrold Green1

Hey Jerry,

Yeah that works, but I have a bunch more columns out to the right with other data that I'm tracking. Adding more columns will clutter up the sheet with doubled information.

I was tinkering around last night with some if statements and it seemed like I came close to figuring out part of the equation, but I ran into issues with the ifcondition.

=if(FS="NG", dur2days(TODAY() - Date), "No NG Flights") will show the ifFalse condition. All of the FS options do this except NS. NS will bring back a value of 59 which is wrong. I know that the 60 day calculation is not entered into that equation but I wanted to see if the correct time frame was being calculated.

I then took out the dur2days section and just made the formula return the date. It picks the 20 Aug, 2011 record even though there are NS flights before and after that record. I don't know if the iftrue section can handle a lot of calculation or if it is just a fluke with the FS="NS" section.

As a side note the FS column has all those options in a pop-up menu.

I'm going to look at it some more today to see if I can find another function similar to sumif with the proper number and use of arguments. I've been able to use it for adding up hours for each FS condition, just need to find the right function to do what I would like to do.

Thanks again for your help, if I find out a way to get it working, Ill post my formula.

Dwayne

Reply

Answer 5

Oct 12, 2011 5:51 AM in response to Aronvorn

Dwane,

The extra columns may be hidden to preserve the visual design of your documsnt.

Jerry

Reply