Cable Forum - Another Excel question!

Cable Forum (https://www.cableforum.uk/board/index.php)

- General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)

- - Another Excel question! (https://www.cableforum.uk/board/showthread.php?t=33601687)

Re: Another Excel question!

Let's resurrect this old post shall we?

It's been brought to my attention that this formula doesn't work correctly - it has a bug....

To recap - I needed a formula to work out overtime between some specific hours - this is for an overtime sheet - we can claim an hours before 07:30 and after 18:30.

We had this formula:

Quote:

=IF(ISBLANK(E10),"None",IF((TIMEVALUE("7:30 AM")-E10)<0,0,TIMEVALUE("7:30 AM")-E10)+IF((F10-TIMEVALUE("18:30"))<0,0,F10-TIMEVALUE("18:30")))

The problem? If you do over in an evening - say 19:00 to 20:00 - that should work out at one hour. The issue is that this formula calculates it as 1.5 hours - I think it's taking the extra half hour from 18:30.

How how can this formula be re-written to remove that bug?

Re: Another Excel question!

I think you'd be better off re-formatting the structure to allow better formulae, rather than trying to do everything in one formula.

Try this....
https://trumpexcel.com/excel-timeshe...ator-template/

Re: Another Excel question!

Quote:

Originally Posted by idi banashapan (Post 35908804)

It really needs to be done with those two columns to keep it very simple. I've tried an extra bit of code but it didn't work and I got confused with it.