Days between dates

[Linda RQ]

Sorry Guys,

I have looking around on a bunch of websites on calculating days between
dates (sort of) and it's almost working but I need it to really work.

I need a cell to return the number of days a patient was on a ventilator.
Here is my latest try. I get the right number but it's a negative number

=DAYS360(G10,F10)-1

Start Date 1/10/08
End Date 1/18/08

The answer should equal 9. I know the difference between these 2 is 8 but
we count each day the patient is on the vent as if it were a whole day.

So what do I need to do to get this answer from these dates?

Thanks,
Linda

 

[Bob Phillips]

=G10-F10+1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

Try this:

= end date - start date + 1

G10 = start date
F10 = end date

=F10-G10+1

Format as GENERAL or NUMBER

 

[Linda RQ]

Crap Bob! That worked. I thought I tried every variation of cell minus
cell and when I found info about the DAYS360 function, I thought I would be
like a real programmer and try it. I'll just bother the group right off the
bat next time when it's something I know is easy for you guys.

Thanks,
Linda

 

[Linda RQ]

Whoops....One small problem. My date fields are formated as Date and Time
behind the scenes, I imported this data from access. I did change the
format to just showing the date but I don't think this removes the time it
only changes how it looks. How can I get this formula to ignore the time
part of the Date or could there be another explaination? I have probably
randomly checked 1/4 of the records and this is the only one that is giving
a goofy answer.


1/10/07 11:11 PM
1/18/08 12:11 PM

Result is 9 which is correct for what I am doing

but

1/27/08 5:00 AM
1/30/08 6:30 PM

Result is 5 and it should be 4

Thanks,
Linda

 

[T. Valko]

Try this:

=INT(G10)-INT(F10)+1

Format as GENERAL or NUMBER

 

[Linda RQ]

Yep...3 records out of 56 were different. The new results are what I am
looking for. Sheesh...what was the problem? If you don't have time to
explain that's ok..I have what I need but I also am curious.

Thanks,
Linda

 

[T. Valko]

Excel store dates as the number of days since a base date. That base date is
January 1, 1900. January 1, 1900 is day 1. January 10, 1900 is day 10. April
11, 2008 is day 39,549.

In Excel a day equals 1. The time of day is the decimal portion of a day.
So, if a day equals 1 then 12:00 PM equals 0.5 which is half of a day.

So, April 11, 2008 12:00 PM has the true underlying value of 39549.5

If some of your dates also included the time then your results were probably
not what you expected. The INT() function rounds down to the nearest integer
and thus removes the time from the date. So:

INT(39549.5) = 39549

 

[Shane Devenshire]

Hi Linda,

If you have a lot of these Date/Time entries then writing a lot of formulas
that require INT may be inconvenient. Here is another solution:

Select all the dates and choose the command Data, Text to Columns, choose
Delimited, click Next, choose Space, click Next, in the Preview pane select
the second column and choose Do not import (skip), and do that for the 3rd
column. Click Finish.

If the data are in two separate columns you will need to execute the command
twice, once on each column.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Shane Devenshire]

Hi Again Linda,

Another solution that you might consider if you are downloading data from
Access on a regular basis. In Access you can create an Append query that
send the date/time field to a Number (Long Integer) field. From there you
can send the data to Excel. There might be some other steps to do in Access
depending on exactly what you are doing. When the date comes into Excel you
will just need to format them as dates, no times will be imported.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Linda RQ]

I get it. Thank-you

Linda

Excel store dates as the number of days since a base date. That base date
is January 1, 1900. January 1, 1900 is day 1. January 10, 1900 is day 10.
April 11, 2008 is day 39,549.

In Excel a day equals 1. The time of day is the decimal portion of a day.
So, if a day equals 1 then 12:00 PM equals 0.5 which is half of a day.

So, April 11, 2008 12:00 PM has the true underlying value of 39549.5

If some of your dates also included the time then your results were
probably not what you expected. The INT() function rounds down to the
nearest integer and thus removes the time from the date. So:

INT(39549.5) = 39549

 

[Linda RQ]

Shane,

Thank-you. I saved these two option to try later. I have to change the
format of my dates when I use my Monarch program too. I would have thought
the 2 Microsoft programs would know how to deal with each other but after
reading Biff's explaination, I "get it"...sort of <g>

Linda

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I get it. Thank-you

Linda