Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kathi
 
Posts: n/a
Default Count number of days between dates BUT IF null to current date

I have a question I'm hoping someone can help me with. I need to know how
many days an invoice was open.
Column A2:A500 is the open date and
Column B2:B500 is closed out date. I need an
"IF B is null" added onto my original =+IF(K4<=J4,1,DAYS360(J4,K4)+1) so
that if there is a closed date then count the number of days between the open
date and close date BUT IF there isn't a close date just count the number of
days from the open date until the current date. Can anyone help?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Count number of days between dates BUT IF null to current date

=MAX(IF(ISBLANK(B2),TODAY(),B2)-A2,1)

Make sure A and B are dates. Be sure to format the cell containing the
formula with something like this, otherwise it will look like a funny date...
# " days"

You would normally just use B2-A2 to get the difference in dates, but in
your case you need a little bit more.
The IF decides wether to use A2 or Today() depending on if A2 is blank.
The MAX portion outputs 1 if the close date is less than A2 (based on your
formula).

There really is no need to ever use the DAYS360 if you understand how excel
works with dates. Look under "How Microsoft Excel stores dates and times" in
the help.

"kathi" wrote:

I have a question I'm hoping someone can help me with. I need to know how
many days an invoice was open.
Column A2:A500 is the open date and
Column B2:B500 is closed out date. I need an
"IF B is null" added onto my original =+IF(K4<=J4,1,DAYS360(J4,K4)+1) so
that if there is a closed date then count the number of days between the open
date and close date BUT IF there isn't a close date just count the number of
days from the open date until the current date. Can anyone help?

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
Count number of cells with date <today's date Cachod1 New Users to Excel 2 January 28th 06 03:37 AM
Count if date is between two dates and value in another column equ mg_sv_r Excel Worksheet Functions 2 December 6th 05 03:31 PM
calculate the number of days from date received Donna Excel Worksheet Functions 7 July 18th 05 07:00 PM
count number of years 2003 in a range of dates Stan Altshuller Excel Worksheet Functions 2 May 3rd 05 07:15 PM
How do I count cells in a column of dates between date ranges? Andrew82 Excel Worksheet Functions 2 April 14th 05 09:59 AM


All times are GMT +1. The time now is 03:37 PM.

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"