Excel formula help

I had this formula created for me a few years ago.  I need to know exactly what is is calculating.  See screen shot of spread sheet.

=H3/(DAY(DATE(YEAR(H$2),MONTH(H$2)+1,0))*24)
excel.png
J.R. SitmanIT DirectorAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
DAY(DATE(YEAR(H$2),MONTH(H$2)+1,0)) gets the number of days in the next month

Close, but not quite :)

DATE(YEAR(H$2),MONTH(H$2)+1,0) tells you what the date of the last day is of the month for whatever date was in H2.  Based on the image, H2 contains a date that is somewhere in March 2013, formatted to mmm yyyy, so that expression returns 2013-03-31.

DAY(DATE(YEAR(H$2),MONTH(H$2)+1,0)) tells you the day number of that date, i.e., 31.

Thus, (DAY(DATE(YEAR(H$2),MONTH(H$2)+1,0))*24) tells you how many total hours there were in that month.

Since H3 tells you the total uptime, H3/(DAY(DATE(YEAR(H$2),MONTH(H$2)+1,0))*24) gives you the percent uptime for that month.
0
 
rspahitzCommented:
Hmmm...let's see...guessing about H2 and H3 but...

YEAR(H$2) gets the year portion of the date in that cell

MONTH(H$2)+1 gets the month portion of that cell and adds one (so determine next month)

DATE(YEAR(H$2),MONTH(H$2)+1,0) will convert the information into next month as a date

DAY(DATE(YEAR(H$2),MONTH(H$2)+1,0)) gets the number of days in the next month

...*24 will calculate hours for that range

H3/... will divide the number of hours next month by some number, maybe for payroll purposes?
0
 
J.R. SitmanIT DirectorAuthor Commented:
So per month how many hours or days is it calculating?  If you look at the attachment each months hours are different and all equal 100%.  That's where I'm confused.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
J.R. SitmanIT DirectorAuthor Commented:
So total hours meaning 31 or 30 or 28 days time 24 hours?
0
 
Patrick MatthewsCommented:
Correct
0
 
J.R. SitmanIT DirectorAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.