Hi Doug, Attched file is updated with required functionality.
Drag the formulaes in column C,D & E as per the data available. Hope that helps. On Sat, Mar 21, 2009 at 7:54 AM, Ashish Jain <26may.1...@gmail.com> wrote: > > > ---------- Forwarded message ---------- > From: Jake <doug_jacob...@intuit.com> > Date: Fri, Mar 20, 2009 at 8:50 PM > Subject: Re: Subtract values that are in different rows > To: Ashish Jain <26may.1...@gmail.com> > > > Ashish, > Here's a sample: > > A B > 1/1/09 12:05 PM Null > Null Null > Null 1/1/09 3:32 PM > Null Null > 1/2/09 9:35 AM Null > Null Null > Null Null > Null Null > Null 1/2/09 2:35 PM > Null Null > > I need to subtract the first non Null value in column A from the first > non Null value in column B and then the second non null value in > column A from the second non Null value in column B and so on down the > line. These date time values represent clock in and clock out times > into a system so subtracting the time in from the time out will give > me the duration of time they were logged into the system. > > > On Fri, Mar 20, 2009 at 2:53 AM, Jake <doug_jacob...@intuit.com> wrote: > >> >> Here's the setup: >> 1. In column A I have date/time stamps. >> 2. In column B I have text entries associated with the time stamps. >> These >> entries vary but each day starts with a date/time stamp of "System >> Login" and >> ends with a stamp of "System Shutdown". Kind of like a time clock >> situation. >> 3. I pull the start times with a VLOOKUP that looks for "System Login" >> and the >> stop times with a VLOOKUP that looks for "System Shutdown". If it >> doesn't find >> them, it enters "Null". >> 4. Now I've got columns C & D with 100 rows that say either "Null" or >> have a >> date/time stamp. >> 5. I now have to subtract the date/time values in column C from the >> matching >> date/time value in column D to get the duration of time worked for >> that >> particular day. >> >> The problem is that the start times and the stop times are in >> different rows, >> e.g. I might have the start time for 3/16/09 in C2 and the stop time >> in D6, the >> start time for 3/17/09 in C7 and the stop time in D10, and so on. Does >> anyone >> know of a formula I can enter in column E that will subtract the first >> date/time >> value in column C from the first date/time value in column D, the >> second >> date/time in column C from the second date/time in column D and so on >> down the >> line? Any help greatly appreciated. >> >> >> > > > -- > Regards > Ashish Jain > > > Sent from: New Delhi Delhi India. > > > > > -- Thanks & Regards, Harmeet Singh --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
Book1.xls
Description: MS-Excel spreadsheet
