Date Count

Hi - I'm trying to create a spreadsheet which contains a column tha
calculates the number of days between dates and then a cell that add
up the total of the dates.
For the date calculation I have tried using =datedif(a1,b1,"d") - thi
is fine if you enter both dates however, if some are to follow i.e yo
have the date to enter in A1 but not b1 then the formula returns #NUM
and consequently the addition cell will not give any result in respec
of other rows where you have been able to enter both dates  - you coul
say well dont enter any data until you have both dates however, th
purpose of the spreadsheet is to record info as its received.  Ideall
I would like the date formula to return 0 if the date for cell b1 i
not yet known.

Sounds confusing but I dont know how to say any other way - can anyon
help

--
martin
-----------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3161
View this thread: http://www.excelforum.com/showthread.php?threadid=52284

0
3/15/2006 9:21:30 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
229 Views

Similar Articles

[PageSpeed] 15

Try:

=IF(ISERROR(DATEDIF(A1,B1,"d")),0,DATEDIF(A1,B1,"d"))

-- 
HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"martins" <martins.24qcjp_1142457904.8309@excelforum-nospam.com> wrote in 
message news:martins.24qcjp_1142457904.8309@excelforum-nospam.com...
>
> Hi - I'm trying to create a spreadsheet which contains a column that
> calculates the number of days between dates and then a cell that adds
> up the total of the dates.
> For the date calculation I have tried using =datedif(a1,b1,"d") - this
> is fine if you enter both dates however, if some are to follow i.e you
> have the date to enter in A1 but not b1 then the formula returns #NUM!
> and consequently the addition cell will not give any result in respect
> of other rows where you have been able to enter both dates  - you could
> say well dont enter any data until you have both dates however, the
> purpose of the spreadsheet is to record info as its received.  Ideally
> I would like the date formula to return 0 if the date for cell b1 is
> not yet known.
>
> Sounds confusing but I dont know how to say any other way - can anyone
> help?
>
>
> -- 
> martins
> ------------------------------------------------------------------------
> martins's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=31616
> View this thread: http://www.excelforum.com/showthread.php?threadid=522849
> 


0
sandymann2 (1054)
3/15/2006 9:40:35 PM
Martins

I hope I understand, try...

IF(OR(A1="",B1=""),0,DATEDIF(A1,B1,"d"))

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"martins" <martins.24qcjp_1142457904.8309@excelforum-nospam.com> wrote in 
message news:martins.24qcjp_1142457904.8309@excelforum-nospam.com...
>
> Hi - I'm trying to create a spreadsheet which contains a column that
> calculates the number of days between dates and then a cell that adds
> up the total of the dates.
> For the date calculation I have tried using =datedif(a1,b1,"d") - this
> is fine if you enter both dates however, if some are to follow i.e you
> have the date to enter in A1 but not b1 then the formula returns #NUM!
> and consequently the addition cell will not give any result in respect
> of other rows where you have been able to enter both dates  - you could
> say well dont enter any data until you have both dates however, the
> purpose of the spreadsheet is to record info as its received.  Ideally
> I would like the date formula to return 0 if the date for cell b1 is
> not yet known.
>
> Sounds confusing but I dont know how to say any other way - can anyone
> help?
>
>
> -- 
> martins
> ------------------------------------------------------------------------
> martins's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=31616
> View this thread: http://www.excelforum.com/showthread.php?threadid=522849
> 


0
3/15/2006 9:44:03 PM
Thanks guys - -problem solved 

--
martin
-----------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3161
View this thread: http://www.excelforum.com/showthread.php?threadid=52284

0
3/15/2006 10:51:15 PM
Reply:

Similar Artilces:

getting notification for system date change
Hi All, can any body please tell me how i can get system date change notification. rgds, Ritu There may be a better way but it looks like you can do it using WMI: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/ creating_a_timer_event_with_win32_localtime_or_win32_utctime.asp -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ritu" <rituc@momentum-tech.com> wrote in message news:e6uHuZdAFHA.904@TK2MSFTNGP12.phx.gbl... > Hi All, > > can any body please tell me how i can get system date change notification. > > rgds, > Ritu >...

Formatting the month of a date
Hello there, I'm extracting the month of a date in a query using Month(date). Since the criteria is for the query to show the records where the month matches the month selected in a combo box (which has the months in letters), I need to add some formating function to Month(date) so that the month in reference shows in letters (like "November") instead of the number ("11"). Thanks! On Thu, 6 Dec 2007 15:21:01 -0800, Alejandro wrote: > Hello there, > > I'm extracting the month of a date in a query using Month(date). Since the > criteria is for th...

how to add a running word count
i want to add a running word count at the end of every page on my essay and is it able to do it automatically? i am using Microsoft Word 2007 You cannot do it automatically. However, if you run a macro containing the following code when your document is the active document, it will create a new document with each page of your document in a separate Section and with the running word count in the footer of each Section. Dim Counter As Long, i As Long Dim Source As Document, Target As Document Dim rngTarget As Range Dim strPages As String Dim arrPagee As Variant Set Source = Ac...

SMART's "Reallocated Sector Count"?
From what I've read so far: 1) Reallocated Sector Count is not the whole story bad-sectorwise. It only reflects sectors that have been remapped to areas other than the designated overflow area for that part of the disc. There may be many more bad sectors that have quietly re-mapped. 2) Through some magic, the remapping process happens when needed. Correct so far? If so, who does the re-mapping? What happens to the file that was using the bad sector? I'm guessing that the part of the file that was using the bad sector has it's bits copied to the new sect...

How to search/sort/filter by Time but not date
Hi there, I need to find out how many emails I have received over the last few months before 9:00am and after 5:00pm. I've tried using search/sort/filter but the date/time field doesn't allow filtering on time alone - it always seems to require a date. I even tried creating a custom column using a formula where I get Hour([Received]) and also tried R-Trim([Received],5) but both these columsn, once created, will not allow you to sort on them. There is another custom column you can create under 'Time/Date' where it has time only, but once created it returns nothi...

Age Calculation with 2 different dates
Hello All: I am relatively new at creating databases and I have hit a snag creating a database for retirees. One of the fields that I have on my form is called "Retiree Age" I figured out how to get Access to only calculate the age up to the DOD for the retiree. However, not every retiree has a DOD. If this is the case then I need the age to be calculated at now. I am figuring that that is a complex if statement but I haven't figured out how to word it correctly. This is what I really want it to do: If Retiree DOD=Null then subtract Retiree DOD from Now, but if Retiree DO...

Auto date
I am trying to create a field in a form that if anything is changed on it, it will automatically update the date to current date. Is this possible? Sojaminc wrote: >I am trying to create a field in a form that if anything is changed on it, it >will automatically update the date to current date. Is this possible? Make sure the last changed date/time field is in the form's record source table/query. Then use the form's BeforeUpdate event: Me.lastchanged = Now -- Marsh MVP [MS Access] Hi - You can do this in the On Dirty event of the form, which fires as soon as you m...

Is there a way to automatically add sequential dates in excell?
Is there a way to automatically add sequential dates to a spreadsheet? For example, creating a spreadsheet for recording rental income, typing in each month manually or is there a function that will automatically fill the cells with the months of the year? genectn, select the cell with your first data, move your mouse over the right bottom corner of the cell, where the little black box is at, you mouse will change to a + sign, RIGHT click and drag down, when you let go of the mouse you will get some options, pick fill months -- Paul B Always backup your data before trying something ne...

Custom Reports and Date Ranges
Is there a way for me to create a filter for date ranges that would effectively be 'tomorrow'? I'm no XML genius, but I've been looking the the reports I have and have noticed that memorized reports with a date-specific filter always memorize the From and To instead of the Range. Some of my custom reports and many of the default reports use ranges like <Today> or <YearStart>. I tried using <Tomorrow> but that didn't work. Is there a way for me to use <Today>+1 or someting similar? Tom -- The worst words in business: "We''ve a...

insert date in Publisher
I created a text box and insert a date with the update date automatically box checked...didn't work why? Publisher 2000 here so it may be a bit different for you. When you Inserted the date, was the box for automatic update checked? -- Don ------- Vancouver, USA "Helpmedates" <u27294@uwe> wrote in message news:67054071f04f2@uwe... >I created a text box and insert a date with the update date automatically >box > checked...didn't work why? > ...

How to make Excel detect the "closest" date to the one I entered and return an value?
Hi all, I have some excel question and hope all the expert here ca help.. last time.. the SUMPRODUCT really helped me a lot and I am stil trying to really understand how it works.. anyways.... .. here is my problem... Col A is a list of date in ascending order, Col B is an numberic value Say in cell C1 , I will enter a random date.. and how can I make Exce to search through Col A and find the closest date to what I hav entered in C1 and return the corresponding Col B value ? Well. not really closest... as long as Col A is a date that is les then and yet the closest . imagine in Col ...

Determining number of weeks between dates
Hello all, wonder if anyone can help me out with this question. In database I need to say how many weeks are left between two dates. Fo example 21/05/04 and 31/03/05. Anyone have any ideas how to do this Any help much appreciated -- goonboy17 ----------------------------------------------------------------------- goonboy175's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2533 View this thread: http://www.excelforum.com/showthread.php?threadid=38810 How accurate do you want it? You could simply subtract one from the other and divide by 7 e.g =(A1/B1)/7 -- ...

Date formula/help
I need to start with the date 12/28/2009 (monday) and drag down to 12/31/2010 (Friday) but I need to skip Saturday and Sunday. Is there an easy way to do this? Hi, With your start date in a1, put this in B1 and drag down =WORKDAY(A1,1) If you get the name error then Tools\Addins and check the analysis toolpak or without the ATP =A1+1+CHOOSE(WEEKDAY(A1+1,2),0,0,0,0,0,2,1) Mike "Confusicous" wrote: > I need to start with the date 12/28/2009 (monday) and drag down to 12/31/2010 > (Friday) but I need to skip Saturday and Sunday. Is there an easy way ...

Date Formula Help #2
I am tring to create a formuls that will allow me to do the following: In cell A1 have a start date, let's say 02/01/05 and in cell B 02/02/05 and so on all the way to the end of the month. But when I get to the end of the month the cell that would contain th date of 02/29/05 would be blank if it is not a leap year and if it is leap year then it would show the date of 02/29/05. Thanks a million for any help -- cummings ----------------------------------------------------------------------- cummingse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2561 View ...

conditional format cells by date
Dear All, I am looking for some help formatting cells with date entries using excel 2003. I have tried using 'formula is' and '=(A1-TODAY())<300', and have seen variations on this theme in other threads, but don't seem to be able to get a formula to work with the following; Date entered into cell (not necessarily 'todays' date). Cell turns yellow when 300 days have elapsed from the date entered, cell turns red when 365 days have elapsed from the date entered (I have the cells formatted for date) Grateful for any help. Thanks. You need to ...

Previous date in List Question
Hello, I have a vertical list of dates in a column, that are designed not to have weekends or holidays in them. They are also in order. In cell A1 I have a date. What formula can I put in cell B2 that will lookup the column of dates, and return the day in the list before the date mentioned in A1? Its some combination of offset and vlookup I believe.... Cheers Here's one way: =INDEX(DateList,MATCH(A1,DateList,0)-1) Format B2 as date. -- Best Regards Leo Heuser MVP Excel Followup to newsgroup only please. "SPR" <spr@mail.com> skrev i en meddelelse news:12d0...

Date Range Totals for Logical Fields
Hi All: I have a database I import from Outlook, and am keeping a list of all active recruits. Each week, I need to update 10 logical fields to see how many more entries were made to each logical question. What is the best way to get my results. I created a query with these fields that did a sum of all these filds, but when I try to add to the criteria true for "Active Recruit", it doesn't work; and if I put a lead date range of "Between #12/1/2007# And #1/31/2008#", It dosn't like that either. I like the way the sum queries work - it's one line with...

Date Question...
I am trying to create a query to give me the results from the first weds of each month back to the following day after weds. Having trouble formatting the Date function. Any help would be greatly appriciated. Thanks David Create a table CountNumber with field CountNUM containing numbers 0 (zero) through 6. SELECT YourTable.* FROM CountNumber, YourTable WHERE ((([SomeDate]) Between (Date()-Day(Date())+1)+[CountNUM] And (Date()-Day(Date())+1)+[CountNUM]-6) AND ((DatePart("w",(Date()-Day(Date())+1)+[CountNUM]))=4)); -- Build a little, test a little. &q...

Convert dates stored as text
I have Excel 2007 in English, but I sometimes receive data that comes from another applicationes, so dates are stored as text because they come in following format: dd/mm/yyyy. And I have the format mm/dd/yyyy. So, in the same column, I have dates stored as dates, and dates stored as text. Which is the easiest way to convert them all to date format? Thanks in advance. Regards, Emece.- Be careful. I'd bet that those values that come in as real dates aren't what the original data represent. For instance, if you have two values: 25/12/2010 and 01/02/2010 The...

Auto Enter of Date if Columns *not* blank.
Hello all. Trying to work out an auto-enter problem, but not really sure how to get started. First, let me outline my spreadsheet. There are 4 coulmns (ALL the other ones are hidden.) Column A contains Text (the reference), Column B contains dollar values (cheque amounts), Colum C contains a number (the number of invoices), and column D contains a date (the date sent.) Now, when I'm doing entries to this sheet, D will ALWAYS equal today's date. So, I usually enter CTRL+; (semicolon) to enter the date. What I want to do is this. If A, B and C are not-blank (ie have values) and D...

=date() default setting returning #name? text in object box
I am developing an access 2003 application for my company, everything works perfectly on my machine and on others machines when they open the split database. However several machines are not displaying the current date in an object that has =Date() set as the default value on a form, and is giving the #Name? text instead. Also several custom buttons do not execute macros and commands on these same machines. I have checked the Access 2003 installations on the problem machines and they appear identical to the good machines. Is there a setting hidden that i am missing? Any ideas. Thanks ...

Preventing date from changing #2
I'm using excel for invoicing purposes. I have it set up so that when I open the master invoice the date is automatically inserted with the "Now" command. My problem is that when/if I open this same invoice at a later date; that original date will change to the current date. I would like the original date to remain. Is this possible? Enter the date using CTRL ; Barb Reinhardt "theslaz" <slaz@home.com> wrote in message news:zMuze.123903$tt5.74430@edtnps90... > I'm using excel for invoicing purposes. I have it set up so that when I > open the master ...

date problem--excel changing entry
I want date to show as 11/05/03 and not have to enter the / or a -. Have formatted cells to date and that but excel ix changing to a totally different date----7/19/06? You could use a macro that converts numeric entries to dates. Chip Pearson has shared his: http://www.cpearson.com/excel/DateTimeEntry.htm Or if you're consistent (leading 0's for days and months), you could format the column(s) as text, do your data entry and then use data|text to columns to convert them to real dates (choose mdy as the field type). D Blount wrote: > > I want date to show as 11/05/03 and...

How do I mark a date on a calendar by placing an X on the date
I am publishing a calendar in Publisher and need to place an X on certain dates to mark them. Is there an easy way to do that? henry wrote: > I am publishing a calendar in Publisher and need to place an X on > certain dates to mark them. Is there an easy way to do that? 1) What version? Different versions work in different ways, you know... In 2003 you can simply type in the boxes. 2) But, then, I much prefer PagePlus for calendars now I've discovered it... -- In memory of MS MVP Alex Nichol: http://www.dts-l.org/ Insert an image http://office.microsoft.com/cl...

save file with tdays date.
Is it possible to set a macro to save a file handle as "real_text_dddd, mm/dd/yy" with the dates populated to the current date? You can't use the slash character as part of a file name (if that is, in fact, what you mean by "file handle"), so you will have to use a different character for it in your filename. You can use the VB Format function to create the date part of your file name; you can use VB's Date or Now function as the argument for the Format function as they both contain the current date (The Now function also returns the time as well as the date, ...