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.

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.09 secs.