Excel formula to calculate date based on 2 cells and how to copy to other cells?

Question

Novice excel user here. Here is a simplified version of my spreadsheet:

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

I want to create formulas to calculate start date and end date based on values in eday and duration columns and begindate:

start date = begindate + eday
end date = start date + duration

So for the above example if I apply a formula, I would expect the following result:

start date  end date    eday    duration
02/11/2020  02/11/2020  -21     4
03/15/2020  03/19/2020   12     0

begindate 03/03/2020 will always be the same.

I was able to get a formula working but when I use the fill handle it doesn't copy the formula correctly.

Please help a novice with these questions:

1. Where is the best place to store the begindate "03/03/2020" - a separate worksheet? hardcode it? other?

2. What is the formula for start date and end date?

3. How do I copy the formula into all rows in the column

Thank you very much.

Answer 1

The first thing I like to remember about dates and times in Excel, is that they are really only numbers, which are formatted to look like dates/times using number formatting. Today's date (07-Sep-2019) is 43715, and the current time (12:20AM) is 0.14302083...

There are worksheet functions that can get the magic number (essentially days since 01-Jan-1900) for you, such as DATE(), TODAY() or NOW().

To that end, assuming your eday column is C and your data starts in row 2, you can enter your start date formula in A2 as =DATE(2020,3,3)+$C2 and your end date formula in B2 as =$A2+$D2. That is the simplest approach.

Note that I have used absolute column references ($C, $A, $D) to force Excel to always look in columns C, A and D, regardless of where I copy/paste or fill, and I have used relative row references (2) so that, if I copy/paste or fill down, the row number will change as part of the filling (i.e. on row 3, the formula would read =$A3+$D3). Sometimes you want this, sometimes you don't, so you should experiment with $ and no $ to see what it does.

I suspect that you originally put your begin date value in a cell, but used a relative reference in your formula and, when you filled down, the relative reference for your begin date changed. If I'm right, then you would need an absolute reference for your begin date cell, e.g. if it was in column J row 1, your formula should read $J$1, not J1.

As you get more confident, you can use more-complicated features to make this more resilient or configurable. For instance:

• You can use the Name Manager to store your fixed, constant date, by creating a new name called BeginDate and setting its value to =DATE(2020,3,3). Now you can update your start-date formula in A2 to be =BeginDate+$C2 and that fixed value will be used everywhere.

• You can format your four-column range as a Table with Headers, which will allow you to use those header names instead of column references. If you format the range as a table, then in the first data row of your start date column (which was A2) you can enter =BeginDate+[eday] and in your end date column (which was B2) you can enter =[start date]+[duration]. Because it is a table, both formulae should fill down automatically, as the table grows. Now apply a simple number format of Short Date to your start date and end date columns.

• You can stop your table showing calculated dates when you haven't entered any values for eday or duration by changing your formula in start date to the more-complicated:

  =IF([eday]="", "", BeginDate+[eday])
  

  ...and changing the formula in end date to:

  =IF(OR([start date]="",[duration]=""), "", [start date]+[duration])