Excel newbie, trying to calculate date differences

[BorisMD]

Here's my scenario:

I am keeping tabs of people who see me for consults and procedures.

I have two columns of dates: consult and procedure

I would like to do two things, first, I would like to add a third
column that will calculate the difference between consult date and
procedure date (if a procedure has, in fact, been done) in number of
days.

Second, I would like to create a cell at the bottom of this third
column that will give me the average of the third column's values
(excluding the fields that are blank, due to no procedure having been
done yet).

Any ideas on how to do this? As I stated, I'm fairly ignorant on the
workings of spreadsheets, so if you have some simple steps that I could
use for this, I would appreciate it.

Thanks,

Boris

 

[Bernard Rey]

I have two columns of dates: consult and procedure

I would like to do two things, first, I would like to add a third
column that will calculate the difference between consult date and
procedure date (if a procedure has, in fact, been done) in number of
days.

Let's say col A is "consult" and col B is "Procedure"

In cell C2, type:

=IF(B2="","",B2-A2)
This will display the difference, or leave the cell blank if no procedure
date has been entered. Copy cell C2 down as much as needed.

Second, I would like to create a cell at the bottom of this third
column that will give me the average of the third column's values
(excluding the fields that are blank, due to no procedure having been
done yet).

Use the "AVERAGE" function should do the job, as it will ignore blank cells.
Something like:
=AVERAGE(C2:C21)

Would be a way to have the answer...

 

[CyberTaz]

Hi Boris-

Bernard's suggestion should do the trick, but you may have to reformat
the cells containing your calculation with Number formatting. Since
your precedent values are dates, the calculation cell might
automatically pick up Date formatting. You may also find that the
AVERAGE() function results in a #DIV/0 error, in which case you might
try:

=IF(E14>0,E14-D14,"N/A") where column D is the consult date & column E
is the procedure date. N/A can be any literal text you want to display
in the cell if the procedure has not been done.

Another option would be to use the DAYS360() function to calculate the
number of days in place of the E14-D14 argument in my example or the
B2-A2 argument in Bernard's.

Good Luck! |:>)

 

[BorisMD]

Hi CyberTaz and Bernard,

I've tried the DAYS360() function as you suggested, and that works well
for the selected cell, but what I don't know how to do is to tell the
spreadsheet to apply that function to the entire column, or
alternatively, easily apply that function to each cell in the column
(without manually inputing the preceding cell names and formula
function for each row).

Thanks,

Boris

 

[Bernard Rey]

I've tried the DAYS360() function as you suggested, and that works well
for the selected cell, but what I don't know how to do is to tell the
spreadsheet to apply that function to the entire column, or
alternatively, easily apply that function to each cell in the column
(without manually inputing the preceding cell names and formula
function for each row).

If I understand it well, you want to copy the formula down. There are
different ways to perform this. Among them (let's say your formula is in
cell C2, and you want it in cells C3 down to C22):

- Edit > Copy cell C2. Then select the C3:C22 range and Edit > Paste,
You'll see the formaula automatically adapts itself.

- Select cell C2. On the right bottom corner, you'll see a small square
dot. Bringing the mouse cursor over it you'll see it change to a black
cross. Click on it and drag it down to cell C22.

- Select the complete C2:C22 range and strike the Control & D keys together

 

[Paul Berkowitz]

- Select the complete C2:C22 range and strike the Control & D keys together

Or Edit/Fill/Down.

 

[BorisMD]

- Select cell C2. On the right bottom corner, you'll see a small
square
dot. Bringing the mouse cursor over it you'll see it change to a black
cross. Click on it and drag it down to cell C22.


Thanks, that worked well. The only thing that I don't like is that the
cells where I have not yet done a procedure, but have done a consult,
now display a large negative number.

I appreciate the help,

Boris

 

[Bernard Rey]

Thanks, that worked well. The only thing that I don't like is that the
cells where I have not yet done a procedure, but have done a consult,
now display a large negative number.

Instead of "DATE360()", you'd rather use this formula:

=IF(B2="","",B2-A2)

That way, the "result" will be blank until a consult date is entered.

 

[BorisMD]

Thanks Bernard,

I finally got that formula to work. I had already used up rows 2-42,
so I had to start with row 43, and use that value instead of 2. I then
extended that equation for the rest of the column and they all work
perfectly now.

Now I'll try to get a floating bottom cell started to get the average
of the filled in values for the column ( e.g. average of all values in
column E, that have a numerical value). I want the cell for the
average to automatically stay at the bottom of the list, even as I add
more patient names (insert rows) to the list.

Thanks again,

Boris

 

[Bernard Rey]

I finally got that formula to work. I had already used up rows 2-42,
so I had to start with row 43, and use that value instead of 2. I then
extended that equation for the rest of the column and they all work
perfectly now.

Now I'll try to get a floating bottom cell started to get the average
of the filled in values for the column ( e.g. average of all values in
column E, that have a numerical value). I want the cell for the
average to automatically stay at the bottom of the list, even as I add
more patient names (insert rows) to the list.

In this case I think the easy way out will be the use of the "List Manager".

- Select the cells you're using (let's say A1:C43)
- From the "Insert" menu, pick the "List" Item
- Click on the "Nest" button twice
- Check the "Show totals row" button on the third page
- Then click the "Finish" button
- From the drop-down menu in the bottom right cell, select "Average"
- Here you are

The list will now adapt to your adjustments: when you'll type in some new
consult, you'll see the list extend to include the new line.

Best is you get a bit familiar with the "List Manager" first, there's a lot
of things to find out around it.