Solved

Excel formula help

Posted on 2013-05-19
6
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

749 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