Calculate hours in last month

[bigandfat]

I am trying to automate an avalability report for uptime and need to calculate the recorded downtime as a percent of avalable time over the last month. I created a nested IF function where IF Jan,744,(IF FEB,672,(IF MAR,744,(IF APR,720,0))) etc., however excel ( MS OFFICE 2000 ) only alows for 7 nested functions, and using this method I would require 12. If anyone has a workaround or another technique I would be greatfull to know.

Thanks in Advance
BigandFat

 

[SkipVought]

Hi,

Use a lookup table.
[tt]
Mon Hrs
JAN 744
FEB 672
MAR 744
...
[/tt]
[tt]
=VLOOKUP(ThisMon,LookupRange,2)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[macropod]

If your date is in A1, you could use:
=IF(OR(MONTH(A1)=4,MONTH(A1)=6,MONTH(A1)=9,MONTH(A1)=11),30,IF(MONTH(A1)=2,28+(MOD(YEAR(A1),4)=0)-(MOD(YEAR(A1),100)=0)+(MOD(YEAR(A1),400)=0),31))*24

You can even avoid the use of an IF formula altogether with:
=(31-(MONTH(A1)=4)-(MONTH(A1)=6)-(MONTH(A1)=9)-(MONTH(A1)=11)-(MONTH(A1)=2)*3+(MONTH(A1)=2)*((MOD(YEAR(A1),4)=0)-(MOD(YEAR(A1),100)=0)+(MOD(YEAR(A1),400)=0)))*24

Not the inclusion of a leap-year test for February.

Cheers

 

[jdhilljr]

Using 'CHOOSE' gives you 29 options and is fairly simple.

=CHOOSE(B1,844,740,740,756,900,877,876,887,889,876,876,345)
in 'B1' you would enter the number for the month.

Jim

 

[TonyJollans]

Hi bigandfat,

Letting Excel tell you how many days there are in the month seems the easiest ..

[blue][tt]=DAY(EOMONTH(A1,0))*24[/tt][/blue]

You will need the Analysis Toolpak AddIn for that to work. Without it, you need the slightly longer ..

[blue][tt]=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))*24[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[bigandfat]

Thank you all for your prompt and knowledgable responses. I used skipvought's Lookup, but seeing all you responses I can see many different solutions to the same problem.

Thank you all
BigandFat

 

[SkipVought]

Lots of ways to skin a cat

;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884