Basic user needs help using dates/ formulas to show target dates - milestones

[London Pete]

I work as a document manager/planner for a construction company and I'm creating a spreadsheet to track the progress of our project drawings and show Milestones:-

I want to try and link data in several columns F, G, H and L; when I enter a value in F (to show a drawing issue date) it should automatically trigger target dates, on the same line, in G, H and L (these columns will indicate dates by which 'Comments' are required - date of 'Planned issue to Client for status' - date of 'Planned Issue of Construction drawing'). Usually we set 7 days between each action. Hope this makes sense?

I believe dates need to be expressed as a number for such a function?


Can you give me any guidance on how to create a formula for such a function? If not then some useful sources or forum websites would be appreciated.

Peter

 

[JoeMo]

I work as a document manager/planner for a construction company and I'm creating a spreadsheet to track the progress of our project drawings and show Milestones:-

I want to try and link data in several columns F, G, H and L; when I enter a value in F (to show a drawing issue date) it should automatically trigger target dates, on the same line, in G, H and L (these columns will indicate dates by which 'Comments' are required - date of 'Planned issue to Client for status' - date of 'Planned Issue of Construction drawing'). Usually we set 7 days between each action. Hope this makes sense?

I believe dates need to be expressed as a number for such a function?


Can you give me any guidance on how to create a formula for such a function? If not then some useful sources or forum websites would be appreciated.

Peter

Say you enter a date in F2 (in typical date format like mm/dd/yyyy), then in G2 enter this:

=IF($F2="","",$F2+7)

and format G2 with a date format. Once a date is entered in F2 this will return a date that is 7 days after the date in F2. Use similar formulas in H2 and L2. With H2 change the 7 to 14, and with L2 change the 7 to 21.

 

[London Pete]

Hi Joe,

Many thanks for that; it worked, obviously!

Could this be expanded to cover a range of lines, or even a whole workbook?

The link I found for this forum came with a comment that it was a really helpful and responsive site! I love reading but have never been one for text books - are there any online (free!) training sites you'd recommend for Excel 2010?

Peter

 

[JoeMo]

Hi Joe,

Many thanks for that; it worked, obviously!

Could this be expanded to cover a range of lines, or even a whole workbook?

The link I found for this forum came with a comment that it was a really helpful and responsive site! I love reading but have never been one for text books - are there any online (free!) training sites you'd recommend for Excel 2010?

Peter

If you want to cover the entire range of data, say in column F:
Select F2 that already has the formula in it.
Scroll down to the last row you want to cover and in column F, hold down the shift key and click the last cell in F you want to contain the formula (you can go beyond your last data row if you want some margin for adding data later).
Now you should see that in column F all the cells from F2 to the last one are selected.
Now hold down the control key (Ctrl) and press the D key (Ctrl + D).
The formula in F2 should now be copied down through the last cell.

There are lots of online sites where you can learn about Excel. Mike Girvin has a UTube site called Excel is Fun at Excel is Fun! that is a good place to start.