How to start counting the hours in a month

Hello All.
I am using a spreadsheet to calculate the hours in each month. I would like for it to start counting each month in 24 hr increments as each month begins and then stops counting after the last day of the month and then in the next months cell start counting again.
For instance,
A1 thru A12 are the months in the year,
B1 thru B12 are the hours accumulated in each cell for each month.

For the months in the future I need them to = 0
For the months in the past I need them to equal the total hours for that month.

I am currently using the following:

HTML Code: 

this references the current date.

The problem with this is I have to wait for the beginning of each mont to enter the formula in order for it to work.

As always you guys are the best resource for help concerning Excel.

Thanks for any and all help

Kenny

Try this,

Please Login or Register  to view this content.

Thanks for your response,
I entered your formula and received the following error;

"You've entered two few arguments for this function"

Kenny

gozillasnack's suggestion is VBA code not a worksheet formula. If you want a formula then, assuming you have the months in text format in A1:A12, e.g. "january" or "jan" then perhaps add a cell with the year, e.g. in C1 enter 2007 then in B1 copied down

=MAX(0,MIN(TODAY(),("1"&A1&C$1)+32-DAY(("1"&A1&C$1)+31))-("1"&A1&C$1))*24

daddylonglegs

This seems to almost work, so I assume I am screwing it up or my description of what I need.
I will try again so please bear with me.

cell A1 = text Month, B1 thru M1 = Jan, Feb, Mar, ect.
cell A2 = text Period Hours, B2 thru M2 = hours in month to date
cell A3 = text In Service Hours, B3 thru M3 = manually entered Service Hours
cell A4 = text Percent Available, Divide Service Hours by Period Hours

All the previous months should have a total of the Period Hours in that month, the current month should only have the current Period Hours and the future months should = 0.

I hope this helps
Thanks for working with me, I will continue to work with your suggestion since it is very close.

Kenny

Hello Kenny,

See attached for two possible approaches.

Version 1 uses the approach I suggested above, whereby you need to select a year.

I think Version 2 is preferable but this needs actual dates in B1:M1, 1st of each month - I've formatted these as "mmm", to show as Jan, feb etc. Also N1 has the first day of the following year, although I've formatted that as ;;; so that it looks blank, this is required for formulas to work correctly

Version 1 needs this formula in B2

=MAX(0,MIN(TODAY(),("1"&B1&$A7)+32-DAY(("1"&B1&$A7)+31))-("1"&B1&$A7))*24

Version 2 only

=MAX(0,MIN(TODAY(),C1)-B1)*24

daddylonglegs,
Thanks so much for your patience and working with me.
I have used your example and it works great.

Thanks once again

Kenny