Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Counting days comparing 2 dates excluding empty cells

I've entered the formula =(L25-M3) in a target cell to count the length of
stay of patients on a hospital unit. L 25 contains the formula to enter the
current date,that is =today(). Each cell in column "M" is the hand entered
admission date. This formula works but I need it to ONLY do the calculation
if there is a value entered in column "M." What argument do I add to
suppress the calculation if "M" is blank? Currently it displays a bizzar
number.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default Counting days comparing 2 dates excluding empty cells

Two ways:

=IF(M3="","",L25-M3)

or

=IF(M3="","",TODAY()-M3)

HTH,
Paul

--

"Terry Rogers" wrote in message
...
I've entered the formula =(L25-M3) in a target cell to count the length of
stay of patients on a hospital unit. L 25 contains the formula to enter
the
current date,that is =today(). Each cell in column "M" is the hand
entered
admission date. This formula works but I need it to ONLY do the
calculation
if there is a value entered in column "M." What argument do I add to
suppress the calculation if "M" is blank? Currently it displays a bizzar
number.




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Counting days comparing 2 dates excluding empty cells

Slick. Thanks
And somewhere in the back of my mind I know the number was "Bizarre" not
"Bizzar."

"PCLIVE" wrote:

Two ways:

=IF(M3="","",L25-M3)

or

=IF(M3="","",TODAY()-M3)

HTH,
Paul

--

"Terry Rogers" wrote in message
...
I've entered the formula =(L25-M3) in a target cell to count the length of
stay of patients on a hospital unit. L 25 contains the formula to enter
the
current date,that is =today(). Each cell in column "M" is the hand
entered
admission date. This formula works but I need it to ONLY do the
calculation
if there is a value entered in column "M." What argument do I add to
suppress the calculation if "M" is blank? Currently it displays a bizzar
number.





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Counting days comparing 2 dates excluding empty cells

Thought the next step would be easy. I now need to subtract 1 from the sum
of the above calculation. These variants produce the correct answer, but
result in an error entry in the target cell again if there is no value in "M"
.. =IF(N4="","",TODAY()-1-N4) ....... =(IF(N4="","",TODAY()-N4)-1)

"Terry Rogers" wrote:

Slick. Thanks
And somewhere in the back of my mind I know the number was "Bizarre" not
"Bizzar."

"PCLIVE" wrote:

Two ways:

=IF(M3="","",L25-M3)

or

=IF(M3="","",TODAY()-M3)

HTH,
Paul

--

"Terry Rogers" wrote in message
...
I've entered the formula =(L25-M3) in a target cell to count the length of
stay of patients on a hospital unit. L 25 contains the formula to enter
the
current date,that is =today(). Each cell in column "M" is the hand
entered
admission date. This formula works but I need it to ONLY do the
calculation
if there is a value entered in column "M." What argument do I add to
suppress the calculation if "M" is blank? Currently it displays a bizzar
number.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default Counting days comparing 2 dates excluding empty cells

I thought we covered that with the IF(M3="",""...portion of the formula.
However, if there is some other value including spaces, then the result
would probably be #VALUE!.

You might try this out.

=IF(ISNUMBER(N4),TODAY()-N4-1,"")

Note: I used the "N" column reference since that is what was in your
example.

Does that help?
Paul

--

"Terry Rogers" wrote in message
...
Thought the next step would be easy. I now need to subtract 1 from the
sum
of the above calculation. These variants produce the correct answer, but
result in an error entry in the target cell again if there is no value in
"M"
. =IF(N4="","",TODAY()-1-N4) ....... =(IF(N4="","",TODAY()-N4)-1)

"Terry Rogers" wrote:

Slick. Thanks
And somewhere in the back of my mind I know the number was "Bizarre" not
"Bizzar."

"PCLIVE" wrote:

Two ways:

=IF(M3="","",L25-M3)

or

=IF(M3="","",TODAY()-M3)

HTH,
Paul

--

"Terry Rogers" wrote in message
...
I've entered the formula =(L25-M3) in a target cell to count the
length of
stay of patients on a hospital unit. L 25 contains the formula to
enter
the
current date,that is =today(). Each cell in column "M" is the hand
entered
admission date. This formula works but I need it to ONLY do the
calculation
if there is a value entered in column "M." What argument do I add to
suppress the calculation if "M" is blank? Currently it displays a
bizzar
number.







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
Counting Empty Dates within a Range? Gina[_2_] Excel Worksheet Functions 3 July 24th 08 06:03 PM
Counting number of days up to an empty cell then start over again SSG QuarterMaster Excel Discussion (Misc queries) 1 May 16th 08 03:28 AM
lowest number in row excluding empty cells Mark Excel Discussion (Misc queries) 5 April 23rd 08 04:23 PM
formula to calculate # of days between dates, excluding holidays abs2299 Excel Discussion (Misc queries) 8 March 3rd 05 03:21 AM
excluding cells from the formula when empty Joe Shell Excel Worksheet Functions 5 November 21st 04 05:37 PM


All times are GMT +1. The time now is 11:20 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"