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.