| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alert if the travel activity is after the work activity
I need to identify (e.g. "alert") travel activities occuring after a work
activity for each unique travel event. My data looks like this... Event Activity Start End C090721 Travel alert 8/4/2009 16:59 8/4/2009 17:00 C090721 Work 8/4/2009 16:47 8/4/2009 16:59 C090721 Travel 8/4/2009 16:46 8/4/2009 16:47 C090722 Travel alert 8/3/2009 12:59 8/3/2009 13:00 C090722 Work 8/3/2009 11:00 8/3/2009 12:59 C090722 Travel 8/3/2009 8:00 8/3/2009 11:00 For travel event, C090721 the travel occuring after the work activity is identified with "alert" |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alert if the travel activity is after the work activity
Is this always ordered by Event then by start date descending?
If so, assuming A2 is the start of your event data and B2 your activity data, put this in C2: =IF(AND(A2=A3,B2="Travel",B3="Work"),"alert","") and copy down. Sam "Go Bucks!!!" wrote: I need to identify (e.g. "alert") travel activities occuring after a work activity for each unique travel event. My data looks like this... Event Activity Start End C090721 Travel alert 8/4/2009 16:59 8/4/2009 17:00 C090721 Work 8/4/2009 16:47 8/4/2009 16:59 C090721 Travel 8/4/2009 16:46 8/4/2009 16:47 C090722 Travel alert 8/3/2009 12:59 8/3/2009 13:00 C090722 Work 8/3/2009 11:00 8/3/2009 12:59 C090722 Travel 8/3/2009 8:00 8/3/2009 11:00 For travel event, C090721 the travel occuring after the work activity is identified with "alert" |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alert if the travel activity is after the work activity
Assuming you only have 1 Work entry per event code:
=IF(AND(B2="Travel",SUMPRODUCT(--(A$2:A$100=A2),--(B$2:B$100="Work"),E$2:E$100)<=D2),"alert","") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Go Bucks!!!" wrote: I need to identify (e.g. "alert") travel activities occuring after a work activity for each unique travel event. My data looks like this... Event Activity Start End C090721 Travel alert 8/4/2009 16:59 8/4/2009 17:00 C090721 Work 8/4/2009 16:47 8/4/2009 16:59 C090721 Travel 8/4/2009 16:46 8/4/2009 16:47 C090722 Travel alert 8/3/2009 12:59 8/3/2009 13:00 C090722 Work 8/3/2009 11:00 8/3/2009 12:59 C090722 Travel 8/3/2009 8:00 8/3/2009 11:00 For travel event, C090721 the travel occuring after the work activity is identified with "alert" |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alert if the travel activity is after the work activity
I only have one work activity per event code so I am using Luke's.
Test successful.. Thanks to you both. "Luke M" wrote: Assuming you only have 1 Work entry per event code: =IF(AND(B2="Travel",SUMPRODUCT(--(A$2:A$100=A2),--(B$2:B$100="Work"),E$2:E$100)<=D2),"alert","") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Go Bucks!!!" wrote: I need to identify (e.g. "alert") travel activities occuring after a work activity for each unique travel event. My data looks like this... Event Activity Start End C090721 Travel alert 8/4/2009 16:59 8/4/2009 17:00 C090721 Work 8/4/2009 16:47 8/4/2009 16:59 C090721 Travel 8/4/2009 16:46 8/4/2009 16:47 C090722 Travel alert 8/3/2009 12:59 8/3/2009 13:00 C090722 Work 8/3/2009 11:00 8/3/2009 12:59 C090722 Travel 8/3/2009 8:00 8/3/2009 11:00 For travel event, C090721 the travel occuring after the work activity is identified with "alert" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to find the date of the last activity | Excel Discussion (Misc queries) | |||
| daily activity time log | Excel Discussion (Misc queries) | |||
| activity duration | Setting up and Configuration of Excel | |||
| Running Activity Chart | Charts and Charting in Excel | |||
| BLANK WORK ORDERS OR DAILY ACTIVITY SHEET TEMPLATES | Excel Discussion (Misc queries) | |||
Linear Mode
