Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel formula help

Posted on 2013-05-19
6
Medium Priority
?
342 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 93

Accepted Solution

by:
Patrick Matthews earned 1400 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

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

Expert Comment

by:Patrick Matthews
ID: 39179911
Correct
0
 

Author Closing Comment

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

Featured Post

Technology Partners: 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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 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