Count no. of Days

[shibulal]

Hi,

I have a sheet with the data of Electricity Payment. I need to calculate the payment on no. of days basis. Eg; billing date of a bill is from 02-02-2012 to 10-04-2012. I need to bring the no. of days (27) for February in column A1 and (31) days for March in column B1 and (10) days for April in column C1.


Is there any formula to calculate those values ?

 

[NARAYANK991]

Hi Shibu ,


Try the following :


1. I assume your Start_Date is in A1 ; the End_Date is in B1.


2. The counts will be in A3 , B3 , C3 , D3 ,....


3. In A3 have the following formula :

[pre]

=EOMONTH(Start_Date,0)-(Start_Date)+1
4.  In B3 and thereafter ( C3 , D3 ,... ) , have the following formula :

=IF(EOMONTH(Start_Date+SUM($A$3:A3),0)<End_Date,DAY(EOMONTH(Start_Date+SUM($A$3:A3),0)),IF(Start_Date+SUM($A$3:A3)<End_Date,DAY(End_Date),""))

[/pre]
If the EOMONTH function is not available check out the following link :


http://excel.tips.net/T002810_Using_the_EOMONTH_Function.html


Narayan

 

[shibulal]

Hi Narayan,


The first formula (=EOMONTH(Start_Date,0)-(Start_Date)+1)turned in to an ERROR- #NAME?


=EOMONTH(Start_Date,0)-(Start_Date)+1, Have I done any mistake while giving the 4mula ?


Regards,

Shibu

 

[NARAYANK991]

Hi Shibu ,


If you get an error when you use the EOMONTH function , it means it has not been added to your software ; check the link in my earlier post :


http://excel.tips.net/T002810_Using_the_EOMONTH_Function.html


on what to do.


Narayan

 

[shibulal]

OK.


Thanks Narayan.

 

[shibulal]

28-Dec-10 28-May-11 4-Jan-1900


Hi Narayan,


Why the value is changed as 04-Jan-1900 ?

 

[oldchippy]

Hi,


As you are working with dates in the adjoining cells, excel assumes the result to be in date format. All you need to do is select the cell/s, right click and format to General

 

[shibulal]

Hey, thanks to all