Hi,
When a service downtime started and ended on the same month I have no problem getting the number of hours as I could just get the difference between the dates. However, I am having trouble calculating the number of hours a service went down if it ended in a different month.
For Example:
Started Ended
Feb X, 2019 hh:mm AM March X, 2019 hh:mm AM
I want to know how many hours the service was down during the month of February excluding the hours from March and vice versa.
Welcome to the forum!
You should be able to get time differences between any dates. Attach a sample workbook that shows the problem here.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try this formula to get total downtime per month
the max around the whole formula is to supress negative outcomes as they can happen.
Please Login or Register to view this content.
also it only works for downtimes less then one calandermonth, so if a downtimes starts in feb and ends in april the formula can be inaccurate
On top of the formula I also used the duration time format which also suppresses 0 values
(because in long tables many 0:00:00 strings can make it hard to read/find actual downtimes)
in the attachment you can see the formula in action
Try this formula
Date in A2
Formula:
Please Login or Register to view this content.
You can format the result as
dd "(days)", hh "hours", mm "minutes and " :ss,0 " seconds"
Thank you so much for this Roel Jongman
Do you have an idea how to calculate it if it goes over more than one calendar month? Pardon for asking much
Last edited by Thienzaw; 03-17-2019 at 07:11 PM.
See if this modification of Roel's formula, as modeled in the attached file, will work for your purposes (where the dates span more than two months).Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you really appreciate the help
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks