Solved

Excel formula help

Posted on 2013-05-19
6
333 Views
Last Modified: 2013-05-19
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
0
Comment
Question by:J.R. Sitman
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39179872
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
 

Author Comment

by:J.R. Sitman
ID: 39179896
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 350 total points
ID: 39179903
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:J.R. Sitman
ID: 39179909
So total hours meaning 31 or 30 or 28 days time 24 hours?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39179911
Correct
0
 

Author Closing Comment

by:J.R. Sitman
ID: 39180126
Thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question