Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Empty Dates within a Range? | Excel Worksheet Functions | |||
Counting number of days up to an empty cell then start over again | Excel Discussion (Misc queries) | |||
lowest number in row excluding empty cells | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
excluding cells from the formula when empty | Excel Worksheet Functions |