A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Count no of nights between dates



 
 
Thread Tools Display Modes
  #1  
Old March 12th 09, 09:39 AM posted to microsoft.public.excel.worksheet.functions
Morten
external usenet poster
 
Posts: 15
Default Count no of nights between dates

I am trying to count no of nights for example stayed in a hotel,

For example
You put in the dates in row 1
Fill then row 2 with "x" if stayed at hotel

Need then another cell to calculate the number of nights that has been
stayed at the hotel (the cells filled with "x").

Can anyone help?

In advance thanks for your help
Ads
  #2  
Old March 12th 09, 09:59 AM posted to microsoft.public.excel.worksheet.functions
RonaldoOneNil
external usenet poster
 
Posts: 117
Default Count no of nights between dates

Use Countif which takes a range and criteria
e.g.
=COUNTF(B2:K2,"x")

"Morten" wrote:

> I am trying to count no of nights for example stayed in a hotel,
>
> For example
> You put in the dates in row 1
> Fill then row 2 with "x" if stayed at hotel
>
> Need then another cell to calculate the number of nights that has been
> stayed at the hotel (the cells filled with "x").
>
> Can anyone help?
>
> In advance thanks for your help

  #3  
Old March 12th 09, 10:00 AM posted to microsoft.public.excel.worksheet.functions
Stefi
external usenet poster
 
Posts: 2,646
Default Count no of nights between dates

=COUNTIF(B:B,"x")
I supposed that you meant COLUMN A for dates and COLUMN B for x's.

Regards,
Stefi

„Morten” ezt *rta:

> I am trying to count no of nights for example stayed in a hotel,
>
> For example
> You put in the dates in row 1
> Fill then row 2 with "x" if stayed at hotel
>
> Need then another cell to calculate the number of nights that has been
> stayed at the hotel (the cells filled with "x").
>
> Can anyone help?
>
> In advance thanks for your help

  #4  
Old March 12th 09, 10:36 AM posted to microsoft.public.excel.worksheet.functions
Morten
external usenet poster
 
Posts: 15
Default Count no of nights between dates

Thanks for the quick response.

I am afraid it does not give me the correct answer. I need the number of
nights between the dates, eg 3 days = 2 nights between 11th March - 13th
March.




RonaldoOneNil skrev:

> Use Countif which takes a range and criteria
> e.g.
> =COUNTF(B2:K2,"x")
>
> "Morten" wrote:
>
> > I am trying to count no of nights for example stayed in a hotel,
> >
> > For example
> > You put in the dates in row 1
> > Fill then row 2 with "x" if stayed at hotel
> >
> > Need then another cell to calculate the number of nights that has been
> > stayed at the hotel (the cells filled with "x").
> >
> > Can anyone help?
> >
> > In advance thanks for your help

  #5  
Old March 12th 09, 01:12 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default Count no of nights between dates

If you need to reduce a number by 1, the function to use is subtraction.
Excel uses a minus sign for subtraction.
If you want an answer of COUNTIF(B2:K2,"x")-1, the formula to use is
=COUNTIF(B2:K2,"x")-1
--
David Biddulph

"Morten" > wrote in message
...
> Thanks for the quick response.
>
> I am afraid it does not give me the correct answer. I need the number of
> nights between the dates, eg 3 days = 2 nights between 11th March - 13th
> March.
>
> RonaldoOneNil skrev:
>
>> Use Countif which takes a range and criteria
>> e.g.
>> =COUNTF(B2:K2,"x")
>>
>> "Morten" wrote:
>>
>> > I am trying to count no of nights for example stayed in a hotel,
>> >
>> > For example
>> > You put in the dates in row 1
>> > Fill then row 2 with "x" if stayed at hotel
>> >
>> > Need then another cell to calculate the number of nights that has been
>> > stayed at the hotel (the cells filled with "x").
>> >
>> > Can anyone help?
>> >
>> > In advance thanks for your help



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count if >0 and between dates Stilmovin Excel Worksheet Functions 14 December 17th 08 05:34 PM
Count on dates Vijay DSK[_2_] Excel Worksheet Functions 16 October 20th 08 04:22 PM
Count Dates Matthew Tyrus Excel Discussion (Misc queries) 2 October 16th 08 06:29 PM
I want to count the total Number of dates between two dates How? seshu Excel Worksheet Functions 3 February 7th 08 05:41 PM
need to convert list of dates to count no. of dates by week neowok Excel Worksheet Functions 13 January 30th 06 03:54 PM


All times are GMT +1. The time now is 12:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.