Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel formula help

Posted on 2013-05-19
Medium Priority
342 Views
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
Question by:J.R. Sitman
• 3
• 2

LVL 22

Expert Comment

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

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

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

Author Comment

ID: 39179909
So total hours meaning 31 or 30 or 28 days time 24 hours?
0

LVL 93

Expert Comment

ID: 39179911
Correct
0

Author Closing Comment

ID: 39180126
Thanks
0

## Featured Post

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. …
###### Suggested Courses
Course of the Month21 days, left to enroll