Forum Discussion
Excel Formula Assistance
Not sure how you want to factor in the shift time, but you probably had the correct formula in G2. Try formatting it (custom format) [h]:mm. Then Excel will keep counting when you exceed 24 hours.
Hi thank you for your input, actually sorry for not mentioning it earlier, that data on G2 was given and not calculated using a formula, if you can give me an idea on how to calculate them properly, then I'll try to think on a way to factor in the daily shifts in between
Faust25 I had entered my formula is in G5. It's visible at the top in the first picture.
=E2+F2-C2-D2It takes End date plus End time minus Start date minus Start time. That gives you a total of 50 hours and 23 minutes. But, I didn't save the file I worked in so now I made a new one, with some formulae and formatting examples. Hope it helps you in fixing your own schedule.
Thanks, now the easy part is done, I just need to find a way to incorporate the working hours that is sandwiched between the downtime start date and end date.... don't even know how to begin on that part.
Faust25 Have a look again at the attached file. Two solutions, neither of them very elegant. One uses several helper formulae to make it easier to follow. Another has all these helper formulae combined into one formula. It's the best I can offer just now.
By the way, the table at the bottom is just to visualise the problem.