Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
I have three columns:
A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
=IF(B1="",TODAY()-A1,B1-A1)
-- Gary''s Student - gsnu200811 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
C2: =IF(B2="",TODAY()-A2,B2-A2)
or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Thank you,. that's perfect! I'd like to insert it throughout the worksheet
but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Well, weird numbers isn't very descriptive, more info needed.
G2: =IF(F2="",TODAY()-A2,F2-A2) or G2: =MAX(MIN(F2,TODAY()))-A2 If these start looking like dates, perhaps you need to format the cells as numbers (with no decimals). -- ** John C ** "Dax Arroway" wrote: Thank you,. that's perfect! I'd like to insert it throughout the worksheet but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Something like:
G: =IF(A20="",ignore,F20="",TODAY()-A20,F20-A20) But that's too many arguments, right? (and not the right syntax I'm guessing) "Dax Arroway" wrote: Thank you,. that's perfect! I'd like to insert it throughout the worksheet but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Startdate and Endate columns are formated throughout the worksheet as dates.
Days counted (Cloumn G) is formatted as General. The formula works perfect but when there are no dates in either columns it gives me a long number in the days counted column (39755 to be exact). I'd the cell left blank until there's a date placed into the startdate column. (There will never be an instance of no end date.) Is that more clear? "John C" wrote: Well, weird numbers isn't very descriptive, more info needed. G2: =IF(F2="",TODAY()-A2,F2-A2) or G2: =MAX(MIN(F2,TODAY()))-A2 If these start looking like dates, perhaps you need to format the cells as numbers (with no decimals). -- ** John C ** "Dax Arroway" wrote: Thank you,. that's perfect! I'd like to insert it throughout the worksheet but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Okay. I assumed a start date was always present. You could use either of the
following: G2: =IF(A2="","",IF(F2="",TODAY()-A2,F2-A2)) or G2: =(MAX(MIN(F2,TODAY()))-A2)*(A2<"") ... this will return a 0 if no start date is entered, so, if you want it blank, use the first formula. -- ** John C ** "Dax Arroway" wrote: Startdate and Endate columns are formated throughout the worksheet as dates. Days counted (Cloumn G) is formatted as General. The formula works perfect but when there are no dates in either columns it gives me a long number in the days counted column (39755 to be exact). I'd the cell left blank until there's a date placed into the startdate column. (There will never be an instance of no end date.) Is that more clear? "John C" wrote: Well, weird numbers isn't very descriptive, more info needed. G2: =IF(F2="",TODAY()-A2,F2-A2) or G2: =MAX(MIN(F2,TODAY()))-A2 If these start looking like dates, perhaps you need to format the cells as numbers (with no decimals). -- ** John C ** "Dax Arroway" wrote: Thank you,. that's perfect! I'd like to insert it throughout the worksheet but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Awesome Possum! That's the one! Thanks a million! --D.
"John C" wrote: Okay. I assumed a start date was always present. You could use either of the following: G2: =IF(A2="","",IF(F2="",TODAY()-A2,F2-A2)) or G2: =(MAX(MIN(F2,TODAY()))-A2)*(A2<"") ... this will return a 0 if no start date is entered, so, if you want it blank, use the first formula. -- ** John C ** "Dax Arroway" wrote: Startdate and Endate columns are formated throughout the worksheet as dates. Days counted (Cloumn G) is formatted as General. The formula works perfect but when there are no dates in either columns it gives me a long number in the days counted column (39755 to be exact). I'd the cell left blank until there's a date placed into the startdate column. (There will never be an instance of no end date.) Is that more clear? "John C" wrote: Well, weird numbers isn't very descriptive, more info needed. G2: =IF(F2="",TODAY()-A2,F2-A2) or G2: =MAX(MIN(F2,TODAY()))-A2 If these start looking like dates, perhaps you need to format the cells as numbers (with no decimals). -- ** John C ** "Dax Arroway" wrote: Thank you,. that's perfect! I'd like to insert it throughout the worksheet but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Day Counting - OPEN & CLOSED
Thanks for the feedback, and you are welcome :)
-- ** John C ** "Dax Arroway" wrote: Awesome Possum! That's the one! Thanks a million! --D. "John C" wrote: Okay. I assumed a start date was always present. You could use either of the following: G2: =IF(A2="","",IF(F2="",TODAY()-A2,F2-A2)) or G2: =(MAX(MIN(F2,TODAY()))-A2)*(A2<"") ... this will return a 0 if no start date is entered, so, if you want it blank, use the first formula. -- ** John C ** "Dax Arroway" wrote: Startdate and Endate columns are formated throughout the worksheet as dates. Days counted (Cloumn G) is formatted as General. The formula works perfect but when there are no dates in either columns it gives me a long number in the days counted column (39755 to be exact). I'd the cell left blank until there's a date placed into the startdate column. (There will never be an instance of no end date.) Is that more clear? "John C" wrote: Well, weird numbers isn't very descriptive, more info needed. G2: =IF(F2="",TODAY()-A2,F2-A2) or G2: =MAX(MIN(F2,TODAY()))-A2 If these start looking like dates, perhaps you need to format the cells as numbers (with no decimals). -- ** John C ** "Dax Arroway" wrote: Thank you,. that's perfect! I'd like to insert it throughout the worksheet but weird numbers are appearing for the rows without start dates. Is there a way of telling it to ingnore them? Also a correction, my colums are A: Startdate F: Enddate G: DayCounter "John C" wrote: C2: =IF(B2="",TODAY()-A2,B2-A2) or, so long as no end dates are in the future, you could eliminate the IF statement: C2: =MAX(MIN(B2,TODAY()))-A2 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Dax Arroway" wrote: I have three columns: A=Start Date B=End Date C=Days I'd like to count days if there is or is not an end date. So if the start date was two weeks ago but there is no end date, I'd like the Days.column to continue to increase as the days go by but end on the date entered into the End.date column. Seems easy but it's the easy things that elude me sometimes. Can someone please help me with a formula for that? Thanks in advance!!! Dax |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference a closed filed from a cell and formula in an open file | Excel Worksheet Functions | |||
Move entire row of data from open to closed file by entering date | Excel Discussion (Misc queries) | |||
Counting Days Open | Excel Discussion (Misc queries) | |||
Show blank cell when not Closed or Open | Excel Worksheet Functions | |||
VBAProject remains open after file is closed | Excel Discussion (Misc queries) |