Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

calculate hours within month

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Calculate Hours within a month

Calculate Hours within a month

ResolvedVersion 2010

Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course

Calculate Hours within a month

Hi

I am trying to calculate the numbers of hours between 2 dates but that lay within a specific month. The dates may cover some, none, or partial part of the month. I have the below formula for a year, but am struggling to convert it to months. Below formula (C is start date), (D is end date), G2 is the year ie 2012.

IF(AND(YEAR($C4)=G$3,YEAR($D4)=G$3),$D4-$C4,IF(AND(YEAR($C4)=G$3,YEAR($D4)>G$3),DATE(G$3,12,31)-$C4,IF(AND(YEAR($D4)=G$3,YEAR($C4)<G$3),$D4-DATE(G$3,1,1)+1,IF(AND(YEAR($D4)>G$3,YEAR($C4)<G$3),365,0))))

Example of my issue ie I want the number of hours in Feb 2016:

start End
01/02/2016 01/03/2016
01/02/2016 02/02/2016
01/01/2016 05/01/2016
01/01/2015 01/04/2016
01/04/2016 01/10/2016

I can get the numbers of hours between the dates, but as you can see this isn't relevant as some/non/all of the time period may fall in February.

Any help would be much appreciated.

Kind regards

Adrian

RE: Calculate Hours within a month

Hi Adrian

I'll get back to you early next week. I understand your aim is to calculate the number of hours in a particular month from any start and end date.

Regards
Doug
Best STL

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Fri 27 May 2016: Automatically marked as resolved.

 

Training courses

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Copying Values Without Formulas in Excel 2010

If you want to copy the contents of a cell but don't want to copy the formula with it then use the following simple method:

Press Ctrl+C to copy the values in the cell. On the Home tab, click Paste and then click Paste Values.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.16 secs.