Forum Discussion
Formula Assistance Needed
Hi Team,
Attached excel file for your reference.
Require assistance for the following below :
1. Station names are stated on column B
2. Station working hours are stated on column D & E
3. Need a formula to only calculate the downtime duration (Column J) that occurred during the station working hours alone. Downtime has been stated as Start Date (Column F), Start Time (Column G), End Date (Column H), End Time (Column I).
4. Need another formula to state if that particular downtime duration is within the working hours or not within the working hours (Column O)
Looking forward towards your assistance.
Thank you.
Left all your columns/formulae in place, but added 22 (!!) helper columns (grouped J:AF) with the end result in AF. The first six columns restructure the dates and time a bit so that it becomes easier to calculate with. Then I do a range of tests that are needed to calculate the overlap of down-time and working hours.
Not very elegant, but still manageable. And I couldn't think of a better way to do it.
6 Replies
Yeah both of us working on the same excel together actually.
Sorry for the confusion.
I have cleared most of the problems on the sheet. Attached for your reference.
Understood that there will be a problem if the value goes negative for Operation Downtime Duration column. Is there any other method that we can try with ? Else then I guess we should calculate it manually and state it.
Kindly advise, please.
Thank you.
Left all your columns/formulae in place, but added 22 (!!) helper columns (grouped J:AF) with the end result in AF. The first six columns restructure the dates and time a bit so that it becomes easier to calculate with. Then I do a range of tests that are needed to calculate the overlap of down-time and working hours.
Not very elegant, but still manageable. And I couldn't think of a better way to do it.
