Solved

Excel formula help

Posted on 2013-05-19
6
332 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:jrsitman
  • 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:jrsitman
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:jrsitman
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:jrsitman
ID: 39180126
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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 Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now