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])
What formula did you try to copy, and how did it end up in the other cells? – jimbobmcgee – 2019-09-06T23:15:22.620