Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time.
In the example in the sheet the load time is 01:00
The start time is 23:00
The end time is 23:35
Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late.
Does anyone have a formula to slove this issue
Hi there,
If I've understood this correctly, the load needed to have been completed by 01:00 tomorrow and was in fact completed by 23:35 today - so why is this operation shown as not having been completed on time?
The problem is that Excel doesn't know that your target is 01:00 TOMORROW - it thinks the target is 01:00 today and so it generates the "Late" indication.
The quick solution to this problem is to enter the target time as 25:00 instead of 01:00 - the cell will display 01:00, but time differences will be calculated on the basis of one day plus one hour.
If you want the target time to be displayed as 25:00 (i.e. 01:00 tomorrow), then use Format > Cells > Number > Custom, and enter "[hh]:mm" (without quotes) in the "Type:" box.
Hope I've understood correctly and that this helps - please let me know how you get on.
Best regards,
Greg M
Last edited by Greg M; 09-16-2007 at 07:51 PM.
I can see where your suggestion comes from but was hoping to work with the system throughout the 24 hour period, which means someones gotta get the maths right in the first place. (they aint that sharp)
Is there a date formula i could apply
eg 01/01/07 01:00 as the loading time without it actually showing the date and then the formula on the Y / N replying with the correct answer based on the header date and time in end column
Trying to cover all eventualities
Hoping this aint gonna get too complex
Last edited by ukphoenix; 09-16-2007 at 08:23 PM.
Hi,
See if this helps......
Sorry last one wrong try this......
Last edited by fletch396; 09-17-2007 at 06:28 AM.
Yes, in M9Originally Posted by ukphoenix
=IF(MOD(K9-J9,1)>MOD(B9-J9,1),"N","Y")
Thanx mate thats right on the money
Sorry I thought it was.
It looks like im gonna have to incorporate dates. The bug I have is in the formula in the Y/N column it looks to be the > sign thats throwing the formula out when used without the date, so I have added date columns.
What i need is a fomula that incorporates the dates returning the correct response in th eY/N column.
I have uploaded a sheet with the details and in row4 it shows N instead of Y, where as row5 returns N which is correct. But i have not used the dates in the formula yet.
Please supply an example of a working formula
Yes, the original formula I supplied will only work if the load by time is less than 24 hours after loading starts, otherwise I think you'll need dates.
It seems to me that you'll have to supply three dates, one each for load by, start loading and finish loading. If you do that then you just need a formula like =IF(C2+D2>A2+B2,"N","Y")
where A2 is load by date, B2 load by time, C2 finish loading date and D2 finish loading time.
Alternatively you could put the time and date in a single cell for each time/date
Hi there,
Maybe using a single cell containing either "This day" or "Next Day" would avoid the need to enter two dates per row. The "On Time" formula would obviously take account of whichever value was selected.
Regards,
Greg M
Hi try this...
in cells B7,J7,K7 custom format dd/mm/hh:mm AM/PM
and in cells L7,M7 custom format [h]:mm
then copy down from each cell
select a cell and change date or time in function bar press enter
or take a look at the attached sheet
hope it helps
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks