Return date of an action

Hi, I have a gantt chart which has columns headed by dates

    A            B         C        D         E     F
1  10/12 17/24     24/12   31/12   1/7
2   Do A
3             Finish B
4                                     Start C
5
6
7

I would like to put a formula in colum F to show the date that the action in 
the row will be completed by. The real worksheet has 52 columns in it so 
using an IF statement isn't an option. Each row will only have one entry in 
it.

Thanks for any help

Ben
9
10



0
Utf
5/27/2010 9:51:19 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
702 Views

Similar Articles

[PageSpeed] 37

So simple but I would never have got there.

Thanks very much

"Jacob Skaria" wrote:

> Hi Ben
> 
> In F2 use the formula and copy down
> =LOOKUP(2,1/(A2:E2<>""),$A$1:$E$1)
> 
> To handle the error for blank rows use the below version
> =IF(COUNTA(A4:E4),LOOKUP(2,1/(A4:E4<>""),$A$1:$E$1),"")
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Ben Kelly" wrote:
> 
> > Hi, I have a gantt chart which has columns headed by dates
> > 
> >     A            B         C        D         E     F
> > 1  10/12 17/24     24/12   31/12   1/7
> > 2   Do A
> > 3             Finish B
> > 4                                     Start C
> > 5
> > 6
> > 7
> > 
> > I would like to put a formula in colum F to show the date that the action in 
> > the row will be completed by. The real worksheet has 52 columns in it so 
> > using an IF statement isn't an option. Each row will only have one entry in 
> > it.
> > 
> > Thanks for any help
> > 
> > Ben
> > 9
> > 10
> > 
> > 
> > 
0
Utf
5/27/2010 8:15:25 PM
If you have excel dates in the 1st row; dont forget to format the formula 
cells to date format

-- 
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

> Hi Ben
> 
> In F2 use the formula and copy down
> =LOOKUP(2,1/(A2:E2<>""),$A$1:$E$1)
> 
> To handle the error for blank rows use the below version
> =IF(COUNTA(A4:E4),LOOKUP(2,1/(A4:E4<>""),$A$1:$E$1),"")
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Ben Kelly" wrote:
> 
> > Hi, I have a gantt chart which has columns headed by dates
> > 
> >     A            B         C        D         E     F
> > 1  10/12 17/24     24/12   31/12   1/7
> > 2   Do A
> > 3             Finish B
> > 4                                     Start C
> > 5
> > 6
> > 7
> > 
> > I would like to put a formula in colum F to show the date that the action in 
> > the row will be completed by. The real worksheet has 52 columns in it so 
> > using an IF statement isn't an option. Each row will only have one entry in 
> > it.
> > 
> > Thanks for any help
> > 
> > Ben
> > 9
> > 10
> > 
> > 
> > 
0
Utf
5/27/2010 9:13:26 PM
Hi Ben

In F2 use the formula and copy down
=LOOKUP(2,1/(A2:E2<>""),$A$1:$E$1)

To handle the error for blank rows use the below version
=IF(COUNTA(A4:E4),LOOKUP(2,1/(A4:E4<>""),$A$1:$E$1),"")

-- 
Jacob (MVP - Excel)


"Ben Kelly" wrote:

> Hi, I have a gantt chart which has columns headed by dates
> 
>     A            B         C        D         E     F
> 1  10/12 17/24     24/12   31/12   1/7
> 2   Do A
> 3             Finish B
> 4                                     Start C
> 5
> 6
> 7
> 
> I would like to put a formula in colum F to show the date that the action in 
> the row will be completed by. The real worksheet has 52 columns in it so 
> using an IF statement isn't an option. Each row will only have one entry in 
> it.
> 
> Thanks for any help
> 
> Ben
> 9
> 10
> 
> 
> 
0
Utf
5/27/2010 9:37:34 PM
Reply:

Similar Artilces:

Date Navigator
I use Outlook 2002 ... this just started happening & I do not know what to do to resolve. Any assistance would be appreciated. In Date Navigator portion of Calendar, January 4, 2007 is bolded even though I have no appointments on that day? How can I get this date unbolded? Thank you - EJS ...

Keeping date in lower case format
Hi, I need to import some data from Oracle that is in ddmmmyy format i.e. 29jan73 The J in jan is lower case in oracle, but when i bring it to excel it formats it to uppercase (29Jan73) Is there any way I can stop this from happening, as I'd like it all to be in lower case? Thanks, Mandy. Hi Mandy, You CAN change it by going to Tools | Options | Custom Lists, and then edit Jan to jan. But then it will be changed for everything. Best regards, Kevin "Mandy Phillips" <phillipm@edgehill.ac.uk> wrote in message news:06d701c398a9$734061c0$a001280a@phx.gbl... > Hi...

calculating hours into date format
hi I would like a formula that will allow a user to input a figure (hours and minutes) into a cell that will calculate the end date and time based on a start date and time. For example, Enter 30 hours, static start time is 01/01/04 00:00hrs, the formula would then calculate that the end figure is 02/01/04 06:00hrs. Essentially it is calculating the number of hours & mins added onto the start date with the end result a date and time later than the hours & mins entered Hope this makes sense! Hi in A1 put your date in B1 enter your hours in the format hh:mm C1: =A1+B1 and forma...

Insert date en time with onchange function
Hi, I have a ntext field in a service indenity . When it is edited i want automaticly insert date en time. Can anyone help? Kind regards Maarten Kievit You may review this website on using Javascript to get time. http://www.tizag.com/javascriptT/javascriptdate.php In your onChange event for that field, you may add var currentTime =3D new Date() var month =3D currentTime.getMonth() + 1 var day =3D currentTime.getDate() var year =3D currentTime.getFullYear() crmForm.all.[Your Field].DataValue =3D month + "/" + day + "/" + year; hope this helps. Darren Liu, Microso...

add a date without holidays
Hi friends, I have a problem, in a macro I need to calculate a date adding working days only. This add not include saturday / sundays and holidays. For example: 21/5 + 6 days = 31/5 How i can to do ? Thanks in advance and sorry by my english, Christian For adding days, use the DateAdd function. It won't return an invalid date like 31/5. The Weekday function returns the number of the day, for instance this Weekday("22.05.2010" ,vbUseSystemDayOfWeek) returns 6 for Saturday, tomorrow it will return 7 for Sunday. For holidays I can't help...

Lookup for oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

date is in 20010129 format, how can I get it in m/d/y format
-- jsanders Data>text to column, go to step three and select date and YMD and for future posts it's considered rude just using the subject line Regards, Peo Sjoblom With the value 20010129 in cell A1, enter this formula in B1: =DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)) and the format the cell as a date. The various functions parse the 20010129 string into its components, and the DATEVALUE converts that string to a datevalue that Excel can recognize. ...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

Please add delete action to IMF for information store
Can *anyone* in the Microsoft Exchange team respond to this: Please can the option to *delete* messages be added to the IMF actions for an information store? Why would we want to do this? We are a K12 school and have two Information stores, one for staff, one for students. It is quite acceptable to deliver spam into the junk email folder for staff. It is *not* acceptable to do this for students. Even though the UCE level is getting set correctly (ie the message is correctly classified as spam) the inability to *delete* such messages from the student information store results in adult m...

Current Date 01-20-10
I would like to filter on todays date. What is the field name for the current date. Thanks. Harry Try "Current Date." - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I would like to filter on todays date. What is the field name for the > current date. Thanks. > > Harry > ...

counting dates #2
in cells D & E i have a series of dates. I want to put a formular E - D = calculated number of days and i also dont want it calculating weekend? the purpose of this is excel to work out how many days it takes to serve a document is this possible and if so what is the formular please =NETWORKDAYS(Start_date,End_date,holidays) Holidays would be a range containing the dates of public holidays you wish to de excluded from your calculation. for example in your case, if you entered the range of holiday dates in cells H1:H10 =NETWORKDAYS(D1,E1,H1:H10) -- Regards Roger Govier "jenn...

Hide Alternate Labels on Date Axis
Hi, I'm having trouble trying to hide every other label along a horizontal date axis. I start on the 01-Jan up to 10-Jan and through using the 'Format Axis'->'Scale'->'Major Unit' and setting this to 2 I can hide the labels: '02-Jan'; '04-Jan'; etc. However, I want to hide the odd dates: '01-Jan'; '03-Jan'; etc. I have tried the various combinations with the dialog box but can't get anything to work. Should I be using a custom formula or something? Any help is much appreciated. Paul. In article <1168177092.892944...

VLookup Returning N/A
Hello: I having a problem with a VLookup formula. My main spreadsheet is a download from a mainframe and the lookup table (on a separate spreadsheet) is inputted directly into Excel. The lookup value is a PO number. All the VLookup Formulas are returning N/A. The weird thing is that if I manually type in the PO Number in the main spreadsheet, then the formulas work. Or if I copy the PO Number from the main spreadsheet into the lookup table, then the formulas work. Obviously the PO numbers in the main spreadsheet and the PO numbers in the lookup table aren't matching, but I can't f...

Subtracting minutes from a date
Is there a way to subtract 15000 minutes from todays date and show the results in date format. A1 = todays date b1= #minutes c1= A1-b1 (the date that many minutes ago) =A1-(B1/(24*60)) Format as Date -- Kind Regards, Niek Otten Microsoft MVP - Excel "J. T. SYLVESTER SR." <sylvester1501@peoplepc.com> wrote in message news:663e99ad.0410011045.29cd532b@posting.google.com... > Is there a way to subtract 15000 minutes from todays date and show the > results in date format. > A1 = todays date > b1= #minutes > c1= A1-b1 (the date that many minutes ago) Su...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

Purcahse Return
How can i receive the amount from vendor against purchase return.i did the full payment against purchase On Aug 22, 9:35 am, qurat <qu...@discussions.microsoft.com> wrote: > How can i receive the amount from vendor against purchase return.i did the > full payment against purchase Hi Qurat, To receive payment against purchase return, there is a workaround available 1. Enter return using "Retuns Transaction Entry" window & post it 2. Enter an "Misc Charges" Entry in "payable transaction entry" window, and apply it against the return. 3. Enter ...

Another question regarding Dates..
First, thanks to the replies to my previous post. How can I work out the day & month when I only know the Year and Day Number? Ie Year = 2004 day number = 302 The answer I'm looking for is 28/10/2004 Again, taking into consideration the leap years Thanks again in advance =DATE(2004,1,302) Regards, Peo Sjoblom "Anthony Slater" wrote: > First, thanks to the replies to my previous post. > > How can I work out the day & month when I only know the Year and Day Number? > > Ie > > Year = 2004 > day number = 302 > > The answer I'...

DoModal() returning -1
I have a class derived from CDialog that I call DoModal() on. Up until recently this was working fine. Now it seems it always returns -1 without appearing (It never hits OnInitDialog()). How can I diagnose this problem? Thanks, Drew Did you remove a control form the dialog template? Or did the ID of the dialog template change. Step into the DoModal method and see where it breaks. AliR. "Drew Myers" <drew.nospam.myers@esrd.com> wrote in message news:eoB8lH7iFHA.2484@TK2MSFTNGP15.phx.gbl... > I have a class derived from CDialog that I call DoModal() on. Up until ...

Public Function to Return ColorIndex
Hello, I made (copied) a function that returns the Index of the background of a cell. I saved it in a module in my Personal.xls workbook. Having done that, I thought I=B4d be able to use it like: =3DGetColorIndex(B5) But... I have to use it like: =3DPersonal.xls!GetColorIndex(B5) Is there a way to achieve the first option, simply =3DGetColorIndex(B5) without the Personal.xls! part before the function? thanks, Fries Hi Fries, One way: In the VBE set a reference to your Personal.xls workbook: Tools | References | Scroll to and tick the Personal.xls project You can then use Perso...

Date an time on WinXP.
Does anyone know where windows Xp gets the time and date it displays from? My system date and time often fails, resulting in the time beiing 5-5mn delayed compared to what it shoul have been . I wonder what can cause that???? ...

how do I stop Excel taking decimal fractions as dates?
sometimes when I enter decimal fractions Excel thinks this is a date. How do I stop this? Hi Insert a single quote before the fraction '3/4 The quote will not show up in the cell, but it will tell Excel to treat the cell as Text. Alternatively, Format>Cells>Number>Fractions 3/4 will display as 3/4 but will be stored as 0.75 and can be used in further calculations. -- Regards Roger Govier "aa" <A@aa.com> wrote in message news:efV2MhV9GHA.4376@TK2MSFTNGP03.phx.gbl... > sometimes when I enter decimal fractions Excel thinks this is a date. > How do ...

i need monthly ledger that is catagorized by payment due dates
"j.marie" <j.marie@discussions.microsoft.com> wrote in message news:9C223E6B-0C7C-46AA-BEAE-2B49F97D4949@microsoft.com... > Nothing. PLEASE write your question in the body of the post, not in the subject line. This is not a chatroom. Thankyou. In answer to your question, you would be better advised to buy an inexpensive dedicated accounting package. That will do what you want "out of the box". ...

Remove time portion from date on Excel 2007 chart
I am trying to remove the auto generated time of 00:00 to every date in my chart. I have tried setting the format, but it just ignores whatever I try to input. Any help would be appreciated. Where does this appear? Which labels? - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ NeoFax wrote: > I am trying to remove the auto generated time of 00:00 to every date > in my chart. I have tried setting the format, but it just ignores > whatever I try to input. Any help would be appreciated. ...

date time problem
I would like to have a date cell that can have an input mask of __/__/__ so that a user need only enter the numerics of a given date; i.e 052405. I I tried setting the cell format to custom mm/dd/yy and I get a return date of 10/1/43 !!!!!???? Why has excel lost the correct date? Regions global show the correct year range, and Excel is set to 1900 time? What do I need to do to correct this? Thanks XL's parse engine and display engine are separate - the displayed format has nothing to do with how the entry is parsed (other than Text entries are not parsed at all). For a workaro...

Custom menu: action is not trigger
Hi, For the first time I am developping an Access application with a custom toolbar (menu) at the top. I have found various posting or web sites where people explain how to do the setup. In fact, I have followed the instructions coming from this web site. http://www.jamiessoftware.tk/articles/menubars.html When my application opens, I can see my custom menu at the top of the form. My problem is the following: when I click on an item in the menu, nothing happen !!! I have double-ckeck and I have really associated an action to the menu item. In fact I have tried to associate the menu item to a ...