Count the hours of each day for the month

**kdraper** · 07-22-2007, 04:26 PM

Hello All,
I was referred here for my problem with summing the hours in the month.
I need to figure the hours available for each month.
Example:
July has 744 hours and if the product is available 744 hours it is 100% available. This is simple once the month is complete, I need to see the percentage during the month.
I would like for Execel to figure the percentage available if only 4, 5 or 6 days of the month have passed by. In other words 96, 120 or 144 hours.
Can Excel automatically count up the hours in each month referencing a cell?

Thanks for any assistance.

Kenny

**Teethless mama** · 07-22-2007, 06:26 PM

Originally Posted by kdraper

Hello All,
I was referred here for my problem with summing the hours in the month.
I need to figure the hours available for each month.
Example:
July has 744 hours and if the product is available 744 hours it is 100% available. This is simple once the month is complete, I need to see the percentage during the month.
I would like for Execel to figure the percentage available if only 4, 5 or 6 days of the month have passed by. In other words 96, 120 or 144 hours.
Can Excel automatically count up the hours in each month referencing a cell?

Thanks for any assistance.

Kenny

Let's say; A1 holds date, and A2 holds number of days have passed

In B1: =(DAY(EOMONTH(A1,0))-A2)*24

The formula above required Analysis ToolPak Add-Ins

**kdraper** · 08-17-2007, 09:28 PM

When I use

HTML Code:

I get #NAME? in that cell.

Kenny

**Greg M** · 08-18-2007, 05:25 PM

Hi there,

If you don't want to use the Add-In then just enter the following code in a VBA module in your workbook:

Please Login or Register  to view this content.

Hope this helps,

Greg M

**Dav** · 08-20-2007, 05:44 AM

if you do not want the toolpak or vba

=(TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)*24 if you want to include todays date

or

=(TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1))*24 if you want it up to yesterday

Regards

Dav

**broro183** · 08-20-2007, 06:42 AM

Hi Kenny,

Originally Posted by Teethless Mama

The formula above required Analysis ToolPak Add-Ins

Originally Posted by kdraper

When I use

HTML Code:

I get #NAME? in that cell.

Kenny

Here are instructions for installing "add-ins" from the Excel Help files just in case you are unaware of how to do this. Add ins are special Excel files which provide extra functionality to Excel by offering extra formulae such as "EOMONTH".
Note: Once the add-in is installed you'll get a result for the above formula rather than the #NAME? error. Also no macros will be required & it provides a shorter/tidier formula than the one suggested by Dav.

(notes were found in the excel Help files under) Load or unload add-in programs
There are different types of add-in programs for Microsoft Excel that provide optional commands and features:

Excel add-ins such as the Analysis Toolpak and the Solver add-in are available when you install Microsoft Office or Excel.
Additional add-ins for Excel can be downloaded from Downloads on Microsoft Office Online.
Developers and solution providers usually design custom Component Object Model (COM) add-ins (COM add-in: A supplemental program that extends the capabilities of a Microsoft Office program by adding custom commands and specialized features. COM add-ins can run in one or more Office programs. COM add-ins use the file name extension .dll or .exe.) and automation add-ins.
You must load an Excel add-in before you can use it.
Load or unload an Excel add-in program

On the Tools menu, click Add-Ins.
Do one of the following:
Load an Excel add-in

If the add-in you want to use is not listed in the Add-Ins available box, click Browse, and then locate the add-in.

Note Add-ins that not available on your computer can be downloaded from Downloads on Office Online.

In the Add-Ins available box, select the check box next to the add-in you want to load, and then click OK.
If the add-in is not currently installed on your computer, click Yes to install it.
If necessary, follow the instructions of the setup program.

hth
Rob

Count the hours of each day for the month

LinkBack

Thread Tools

Rate This Thread

Display

Count the hours of each day for the month

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions