Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default Days between dates

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








  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Days between dates

=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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










  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Days between dates

Try this:

= end date - start date + 1

G10 = start date
F10 = end date

=F10-G10+1

Format as GENERAL or NUMBER


--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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










  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default Days between dates

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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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












  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default Days between dates

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




"Linda RQ" wrote in message
...
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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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


















  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Days between dates

Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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




"Linda RQ" wrote in message
...
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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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


















  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default Days between dates

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" wrote in message
...
Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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




"Linda RQ" wrote in message
...
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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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






















  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Days between dates

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

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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" wrote in message
...
Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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




"Linda RQ" wrote in message
...
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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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
























  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 857
Default Days between dates

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

"Linda RQ" wrote in message
...
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








  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 857
Default Days between dates

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" wrote in message
...
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










  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default Days between dates

I get it. Thank-you

Linda

"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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" wrote in message
...
Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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




"Linda RQ" wrote in message
...
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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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


























  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default Days between dates

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


"Shane Devenshire" wrote in message
...
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" wrote in message
...
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











  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Days between dates

You're welcome!

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
I get it. Thank-you

Linda

"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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" wrote in message
...
Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Linda RQ" wrote in message
...
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




"Linda RQ" wrote in message
...
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

"Bob Phillips" wrote in message
...
=G10-F10+1

--
---
HTH

Bob


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



"Linda RQ" wrote in message
...
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




























Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calc days between two dates and exclude leap year days scoz Excel Worksheet Functions 5 November 23rd 07 04:58 PM
Conditional Formatting Dates calculating 10 days and 30 days from a certain date Sioux[_2_] Excel Worksheet Functions 2 October 11th 07 02:04 PM
days and dates msiz Excel Discussion (Misc queries) 1 October 23rd 06 04:24 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Dates / Days Jeff Excel Discussion (Misc queries) 1 February 14th 05 06:37 PM


All times are GMT +1. The time now is 09:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"