Home News Forum Articles
  Welcome back Join CF
You are here You are here: Home | Forum | Another Excel question!

You are currently viewing our boards as a guest which gives you limited access to view most of the discussions, articles and other free features. By joining our Virgin Media community you will have full access to all discussions, be able to view and post threads, communicate privately with other members (PM), respond to polls, upload your own images/photos, and access many other special features. Registration is fast, simple and absolutely free so please join our community today.


Welcome to Cable Forum
Go Back   Cable Forum > Computers & IT > General IT Discussion

Another Excel question!
Reply
 
Thread Tools
Old 20-07-2017, 16:35   #31
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
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?
  Reply With Quote
Advertisement
Old 22-07-2017, 22:04   #32
idi banashapan
step on my trip
 
idi banashapan's Avatar
 
Join Date: Jul 2003
Posts: 3,714
idi banashapan has a nice shiny star
idi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny staridi banashapan has a nice shiny star
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/
__________________
If one does not understand a person, one tends to regard him as a fool
idi banashapan is offline   Reply With Quote
Old 22-07-2017, 23:11   #33
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

Quote:
Originally Posted by idi banashapan View Post
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/
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.
  Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 17:49.


Server: osmium.zmnt.uk
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.