Inclusive number of days and running total

Hi,
I have a column of start dates (B) and a column of end dates (C).
I would like to have the inclusive total of days.

I would then like to have a running total of days for the callendar year as well as the total for any year and a grand total.

Have been playing with DATEDIF but no luck so far.

Cheers,

Chris
0
Chris6982 (632)
8/3/2004 6:53:01 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
842 Views

Similar Articles

[PageSpeed] 42

Just subtract the date in C from the date in B and format as General.

Then SUM as required.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:5744B2E0-015B-40C1-B9C1-ABDD5D62BC4B@microsoft.com...
> Hi,
> I have a column of start dates (B) and a column of end dates (C).
> I would like to have the inclusive total of days.
>
> I would then like to have a running total of days for the callendar year
as well as the total for any year and a grand total.
>
> Have been playing with DATEDIF but no luck so far.
>
> Cheers,
>
> Chris




0
bob.phillips1 (6510)
8/3/2004 7:02:30 AM
Hi, Bob,
Unfortunately this doesn't give the inclusive number of days (18/1/04 - 18/1/04) gives the answer 0. For inclusive, it should be 1.
WhenI try to +1 at the end of the DATEDIF, it doesn't work.

Chris

"Chris" wrote:

> Hi,
> I have a column of start dates (B) and a column of end dates (C).
> I would like to have the inclusive total of days.
> 
> I would then like to have a running total of days for the callendar year as well as the total for any year and a grand total.
> 
> Have been playing with DATEDIF but no luck so far.
> 
> Cheers,
> 
> Chris
0
Chris6982 (632)
8/3/2004 8:09:01 AM
Just ad 1 to the formula if you want inclusive.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:60980A71-6220-44A0-93AD-B36B4841D9DC@microsoft.com...
> Hi, Bob,
> Unfortunately this doesn't give the inclusive number of days (18/1/04 -
18/1/04) gives the answer 0. For inclusive, it should be 1.
> WhenI try to +1 at the end of the DATEDIF, it doesn't work.
>
> Chris
>
> "Chris" wrote:
>
> > Hi,
> > I have a column of start dates (B) and a column of end dates (C).
> > I would like to have the inclusive total of days.
> >
> > I would then like to have a running total of days for the callendar year
as well as the total for any year and a grand total.
> >
> > Have been playing with DATEDIF but no luck so far.
> >
> > Cheers,
> >
> > Chris


0
bob.phillips1 (6510)
8/3/2004 11:43:46 AM
Chris,

=C2-B2 + 1, copied down.  It will likely format itself for a date
(meaningless), so format the formula cell for an ordinary number -- Format -
Cells - Number:

General
Number

For totals, sum the formula column
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:5744B2E0-015B-40C1-B9C1-ABDD5D62BC4B@microsoft.com...
> Hi,
> I have a column of start dates (B) and a column of end dates (C).
> I would like to have the inclusive total of days.
>
> I would then like to have a running total of days for the callendar year
as well as the total for any year and a grand total.
>
> Have been playing with DATEDIF but no luck so far.
>
> Cheers,
>
> Chris


0
nowhere1083 (630)
8/4/2004 1:30:14 AM
Earl,
That worked - thanks.
Now the problem is that I have some data missing in either column B or C which gives invalid data in D (inclusive total days) for that row. How do I inhibit it from making an invalid calculation (it works out from yr1900!)

A knock-on from that is the ability to have a running total that doesn't become invalid when one of the cells contains an invalid calculation. How?

Cheers and many thanks to all you Excel wizards!

Chris


"Earl Kiosterud" wrote:

> Chris,
> 
> =C2-B2 + 1, copied down.  It will likely format itself for a date
> (meaningless), so format the formula cell for an ordinary number -- Format -
> Cells - Number:
> 
> General
> Number
> 
> For totals, sum the formula column
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
> 
> "Chris" <Chris@discussions.microsoft.com> wrote in message
> news:5744B2E0-015B-40C1-B9C1-ABDD5D62BC4B@microsoft.com...
> > Hi,
> > I have a column of start dates (B) and a column of end dates (C).
> > I would like to have the inclusive total of days.
> >
> > I would then like to have a running total of days for the callendar year
> as well as the total for any year and a grand total.
> >
> > Have been playing with DATEDIF but no luck so far.
> >
> > Cheers,
> >
> > Chris
> 
> 
> 
0
Chris6982 (632)
8/4/2004 2:57:01 AM
Chris,

=IF(AND(B2>0,C2>0),C2-B2+1,"")
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:4C71AE93-78E4-4275-AB75-C62082D45EB5@microsoft.com...
> Earl,
> That worked - thanks.
> Now the problem is that I have some data missing in either column B or C
which gives invalid data in D (inclusive total days) for that row. How do I
inhibit it from making an invalid calculation (it works out from yr1900!)
>
> A knock-on from that is the ability to have a running total that doesn't
become invalid when one of the cells contains an invalid calculation. How?
>
> Cheers and many thanks to all you Excel wizards!
>
> Chris
>
>
> "Earl Kiosterud" wrote:
>
> > Chris,
> >
> > =C2-B2 + 1, copied down.  It will likely format itself for a date
> > (meaningless), so format the formula cell for an ordinary number -- 
Format -
> > Cells - Number:
> >
> > General
> > Number
> >
> > For totals, sum the formula column
> > -- 
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "Chris" <Chris@discussions.microsoft.com> wrote in message
> > news:5744B2E0-015B-40C1-B9C1-ABDD5D62BC4B@microsoft.com...
> > > Hi,
> > > I have a column of start dates (B) and a column of end dates (C).
> > > I would like to have the inclusive total of days.
> > >
> > > I would then like to have a running total of days for the callendar
year
> > as well as the total for any year and a grand total.
> > >
> > > Have been playing with DATEDIF but no luck so far.
> > >
> > > Cheers,
> > >
> > > Chris
> >
> >
> >


0
nowhere1083 (630)
8/4/2004 3:47:09 AM
Reply:

Similar Artilces:

Number of columns
Is there any way to make excel have more than 256 columns, from A to IV? No. In article <#izxqyniEHA.3348@TK2MSFTNGP12.phx.gbl>, "Gunawan" <jgunawan98@hotpop.com> wrote: > Is there any way to make excel have more than 256 columns, from A to IV? "Gunawan" <jgunawan98@hotpop.com> wrote in message news:<#izxqyniEHA.3348@TK2MSFTNGP12.phx.gbl>... > Is there any way to make excel have more than 256 columns, from A to IV? Possible Workaround: Use multiple worksheets. Mark Wolven hu kiteb: > "Gunawan" <jgunawan98@hotpop.com>...

negative numbers to positive numbers in excel
How can I convert negative numbers to positive numbers in excel have a look in HELP index for ABS -- Don Guillett SalesAid Software donaldb@281.com "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:4ADD359E-E053-44E9-A760-C2235EDA70ED@microsoft.com... > How can I convert negative numbers to positive numbers in excel If it's a recurring need, one way: B1: =ABS(A1) If it's a one-time need, one way: Put -1 in a cell. Copy the cell. Select the cells to negate. Choose Edit/Paste Special, selecting the Values and Multiply radio buttons. In ar...

Large number of dynamic charts
I have an excel file of 19 sheets, each sheet has nine data coloumns from b to j. I made nine offset() to plot these data. But, its not working. Here there are 171 offset() to make the required 171 graphs in thoses sheets. There are nine graphs for each sheet. Any one can help please. Is the problem related to capability of excel? Kh I've read your post several times and am not sure what you are doing. I'll need more details to help. Can you post a sample of your data from one sheet and your actual offset formulas. .. "kh" <kh@discussions.microsoft.com> wrot...

Auto generate document number for eConnect
Hi, in great plains I can ingress a document with auto generated document number, how can I transfer a document (using eConnect) without document number (DOCNUMBR)???? I want this document number be auto generate like great plains. Regards Carlos Suma ...

Large Numbers in CRM
What do you do if you have a client that needs a number greater than 100 Billion? SQL server can handle much larger numbers, why does MSCRM limit the size? Use Float then. "X-eqt" <Xeqt@discussions.microsoft.com> píše v diskusním příspěvku news:E1B83F0C-C0D8-44BD-AA74-582712559DB2@microsoft.com... > What do you do if you have a client that needs a number greater than 100 > Billion? SQL server can handle much larger numbers, why does MSCRM limit > the > size? ...

$997 PROFIT Per Order
Earn $3500-$6000 a Week. Work with Top Money Earner 1 on 1 Not MLM Never Pick Up a Phone Not Predator or Coastal 100% automated 100% internet based business See how Average Joes are earning $20,000 their first month Get the details here www.0to20k.com ...

Page numbers all the same
I am using Word 2007 to produce documents with uniform formating. A template was created for me with line art and line numbering down the left side. A header includes the page number and the document title. A footer contains the same line of text on every page. The template was created using Word 2003. When I open the template with 2007 the page numbering is page 1 on every page. When I change it on page 2, the line art and numbering down the side disappear. What is happening? I have tried working in compatability mode and the same thing happens. Whoever created the tem...

Store numbering
Does RMS care if stores are sequentially numbered? I.E. if i have 5 stores can I number them 1,13,14,15,21? Or does that mess with the licensing? -- Pat pat@arms wrote: > Does RMS care if stores are sequentially numbered? I.E. if i have 5 stores > can I number them 1,13,14,15,21? Or does that mess with the licensing? The StoreCode is not sequentially numbered... I use 120, 250, 377.. 686 ... the code of main supplier of stores to identify electronic-invoice, orders and others bye antonio ...

Trying to display number and text in same cell
Hi, I have a basic question which I still can't resolve even after finding the specific section on the subject in the Excel 2002 help file (Grrr!). I want to enter a number in a cell, and have it treated by Excel as a number, and after the number in the same cell, write free form text appropriate for that particular number alone. If I do that for 3 rows for example, I want the ability to add the numbers in the resulting column, such as the following: cell A1 says: $5 value of John's cars cell A-2 says: $2 value of Sue's stock cell A-3 says: $7 value of Bill's comp...

Extract numbers
Hi, I have a field in my database where it's written "1d" or "12d" ecc.. I want to use an equation in a query to display the number included in this field only,meaning another field where it's written "1" or "12" how can i do this ? Try --- Val([YourField]) -- KARL DEWEY Build a little - Test a little "Pietro" wrote: > Hi, > I have a field in my database where it's written "1d" or "12d" ecc.. > I want to use an equation in a query to display the number included in this > field only,me...

Another Day With No Stock Update!
Thank you Microsoft. It's another day with slow update responses followed by no stock update or today it choose to hang. What could the problem be? Is it your servers? Is it Money? Would you continue to support purchasing Money with such unpredictable behavior? Well, it is predictable if you are willing to accept poor service. Does Bill use Money? I can not believe he would accept such crap or does he use Quicken? Or is it your system. I am not having any problems. Bob <NoStockUpdate <Boycott@MS_Money.com>> wrote in message news:2gk6ovg18cg6nvbdt37jd01i6n2h9jahg8@4a...

New! Consecutive Numbering Tutorial for Publisher 2002/2003
http://www.mvps.org/publisher/numbering1.html Brian Kvalheim Microsoft Publisher MVP http://www.kvalheim.org ...

Custom date format for days ... again ... thx <g>
Sorry about this but a recent question re custom date format was a special case type of scenario and I'm finding that I still don't know how to change to my preferred date format for most spreadsheets. The new sheet I'm trying to fix might show how to do this once and for all. In today's sheet I have 2 cells, B2 is formatted as "ddd" and C2 as "mmm.dd.yyyy". C2 is a date entered by user where B2 is dependent on C2 via the formula "=C3". I was hoping to finally learn how to get XL to change the "ddd" format of" Sun, Mon, Tue, Wed...

group of numbers
How can I make a bunch of numbers being recognized as a group? I've got the following numbers: 1, 2, 3, 4, 5 and 6. I want them to be considered as a group. How can I achieve it? Thank you for your attention --- Message posted from http://www.ExcelForum.com/ You can name the range of cells that contain the numbers. There are instructions here: http://www.contextures.com/xlNames01.html If that's not what you're trying to do, perhaps you could give a bit more detail. PeaceMaker < wrote: > How can I make a bunch of numbers being recognized as a group? > > I&...

Number not showing correctly
Hi, I had tried to enter a 12digit number in a worksheet but after entering it its not showing the coorrect number.Eg Iam trying to enter 857268578633 in the work sheet but after entering its showing 8.57269e+11 Why its happened Like this can you please explain me the format of the cell is probably 'General' instead of number. When you select number you can select the number of decimal places and/or the comma and negative number format if you like. If it then shows up as ###### or still has the scientific format appearance, you can widen the column. "Nawas" wrote: ...

Outlook fails to connect to exchange every second day.
Hi, I have a client who uses exchange and asked about a bizzare behaviour they have been experiencing. Apparently every (approximate) two days when they come into the office their outlook will fail to connect to exchange. What they have been doing to fix this is to Reboot exchange. For some reason this is working. What I am hoping to find is a "fix" that doens't require a reboot. :) There seems to be an error in the event log indicating that the exchange database is larger than it is allowed to be. Could this cause the experience they are seeing? Any clu...

transaction number #2
hi all while generating detailed sales report in mrms 1.3 we select transaction number to view journal of that particular bill,we find correct details in journal. But when we see in detailed sales report , we find items which are not a part of that journal displayed under transaction #. Is there query any problem in detailed sales report query in qrp reports. Rizwan, Have you tried to reindex the database? It's in the RMS Administrator under the database menu. Are you having any other issues with the software? Daniel techs1@nurol.com NuRol Point of Sale www.nurol.com 404-352-...

Numbering
Hi, I am new to Word 2007 and am looking for a little be of help. hopefully someone knows the answer. I am creating a document with various headings. Having set-up my styles I have now encoutered a problem. I have various sections and the numbering is ok for Section A, however, when I reach Section B although the main heading is ok "2 SECTION B" (the numbering following from the main heading of SECTION A), the main body numbering follows of from Section A and does not restart. See example below At the present the document looks like this: 1 SECTION ...

automatic total of selected cells
Usually if I select a column or row of cells, I can see the total at the bottom of the worksheet - this seems to have disappeared. How do I get it back? I know this is dead simple, but I can't find the answer in help. The area is called the Status Bar. Right-click the Bar and select the desired function to appear there. -- Gary''s Student - gsnu200826 "MarlboroughT&C" wrote: > Usually if I select a column or row of cells, I can see the total at the > bottom of the worksheet - this seems to have disappeared. How do I get it > back? I know this is de...

How to get the digit number of a LONGLONG data?
How to get the digit number of a LONGLONG data? for example:LONGLONG aaa=123; then the digit number is 3; LONGLONG aaa=5678; then the digit number is 4; Another way is: int digits = 0; LONGLONG number = aaa; while (number > 0) { number /= 10; digits++; } Geo >-----Original Message----- >How to get the digit number of a LONGLONG data? >for example:LONGLONG aaa=123; then the digit number is 3; > LONGLONG aaa=5678; then the digit number is 4; > > >. > As long as the number isn't negative. "Geo" <ckarakiro@hotm...

Public folder has many numbered email addresses
Exchange Enterprise Edition 6.5 (6944.4) When I review the email addresses on one of the top level public folders it has a very large number of SMTP entries. Here are a few examples: 10254d@domain.com 1027cd@domain.com 103654@domain.com 103b26@domain.com .... I have no idea where they came from. I created another folder at the root level and it does not have these. Has anyone seen this before or might be able to shed some light as to how they ended up in there? Over the last few days I've been perfroming an inter-org migration from Exchange 5.5 but I did not migrate public folders. ...

Routine does not run when workbook is opened
I have a routine in workbook1 that opens and updates a number of other workbooks when workbook1 is opened. Everything was fine until I got a new computer (both PCs are running XP SP3 and Excel 2003). Now workbook1 will not open or run when I try to open it from windows explorer or from scheduled tasks. The only way I can open workbook1 is to start Excel and then use file open and press cntrl and shift while double clicking on workbook1. Please help! Thanks, Raul The problem turned out to be a difference in the file paths specified in the program and the file paths on th...

Error Number: 0x800CCC0E
Respected, I need your help. I cant find answer from my gmail account settings searching the provided error in subject area. The problem I have is that I can't send e-mails through my Outlook Express 2003 but I can receive them. Anny suggestions? Thank you in advance. Here's two suggestions: 1. Post the complete error message. 2. Setting up Outlook Express to access Microsoft newsgroups http://www.michaelstevenstech.com/outlookexpressnewreader.htm Lina wrote: > Respected, > I need your help. I cant find answer from my gmail account settings > searching the provi...

Convert numbers to roman numbers
I am extracting data from external data base into a query. I have a field for Catergory which pulls numbers 1,2,3. I need to change them to Roman numbers I, II, III in order to work with my classification. I can't figure out a way to accomplish that in access 2003. Can someone help? That's why the Romans had so much trouble with arithmetic:) There is no way to derive the roman numeral values. The best solution is a cross-reference table. You keep the normal number field in the table for sorting purposes but use a lookup table to get the RN value for display. "Waheeda ...

Add working days to date...
Hi all, I would like to be able to add a number of working days to a given date to determine the 'due-by' date. Any ideas how I might do this. The formulae would need to accommodate a list of public holidays in the way the NETWORKDAYS function does. Any hideas greatly appreciated. Cheers! jim Look at the WORKDAY() function in help. In article <8d4d01c3ea61$d14b5bd0$a301280a@phx.gbl>, "jim" <anonymous@discussions.microsoft.com> wrote: > Hi all, > I would like to be able to add a number of working days to > a given date to determine the 'due-by...