Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
days and dates | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Dates / Days | Excel Discussion (Misc queries) |