Fixing time range

[moody]

Hi all, first post here so pls pardon me if my question is not clear.


I'm in production planning and i would like to see the output of the production from , lets say 8.05am of 10th Aug to 8.05am of 11st Aug.


The data is like this where the data i get should stop at 8.05am which means it should classify Lot B as the production output for 10th Aug , and not Lot A instead


column A Column B Column C

9th Aug 9.35am 10th Aug 7.35am Lot A

9th Aug 7.35am 10th Aug 8.35am Lot B


Hope i'm not long winded..

 

[Luke M]

Are you just wanting a count of the jobs that fit in time range?

It appears col B is the only column that matters in your example, so this should work:

=COUNTIF(B:B,">="&StartTime)-COUNTIF(B:B,">"&EndTime)

 

[moody]

Hi Luke,


thanks for the reply but unfortunately,thats not my aim.


column A Column B Column C Column D

9th Aug 9.35am 10th Aug 7.35am Lot A 200pcs

9th Aug 7.35am 10th Aug 8.35am Lot B 1500pcs

10th Aug 9.35am 10th Aug 11.35am Lot C 500pcs


Then i wish the results will be

1) Lot A is classified as 9th AUg output coz End time is Before 8.05am 10th Aug

2) Lot B &C is classified as 10th AUg o/p coz End time is after 8.05am

10th Aug

I tried to use =INT(End time) but it seems that is not able to get the results by time , only the date..

Maybe only macro is able to do this?please help

 

[Luke M]

So, you just need to know which date to classify each line? How about:

=INT(B2-TIME(8,5,0))


You may need to adjust the formatting of the cell to only display date.

If this isn't what you want, could you post a clear example of what you would like the output of the formula to be?

 

[moody]

Hi Luke.


thats really fast of you..

Yes..thats about it.The formula should be =INT(C2-TIME(8,5,0))was correct because i want the END date after 8.05am .


My fault for not showing you th correct date/time format.

It should be 8/13/11 7:25


Last thing.This formula works only if the start date and end is only a difference of 1 day.If the lot took more tha 1 day, for example: 8/13/11 7:25 to 8/15/11 8:25

the results i get will be 14th AUg instead.


This is the results i got

start End Classified date

8/12/2011 23:31 8/13/11 7:25 12-Aug

8/13/2011 23:31 8/14/11 8:25 14-Aug

8/12/2011 7:25 8/14/11 13:25 14-Aug

8/13/2011 7:25 8/15/11 11:25 15-Aug

8/14/2011 7:25 8/16/11 4:25 15-Aug


If you really need a sample of data , maybe i could upload to you next week.


Thanks~!

 

[moody]

i mean the date/time format is m/d/yy h:mm

 

[Luke M]

I'm not able to duplicate your result with the formula. When I have 8/15/11 8:25, formula output is Aug 15th.


Again, no part of your question/logic seems to be affected by start day, so it shouldn't matter how many days it takes. If the start day somehow affects the "output day", I need you to state what impact it hows.

 

[moody]

Hi Luke,

you're right.

When I mention that "This formula works only if the start date and end is only a difference of 1 day.If the lot took more than 1 day, for example: 8/13/11 7:25 to 8/15/11 8:25

the results i get will be 14th AUg instead'


THIS IS actually correct.I make myself confused,I did want the date to show current date minus 1 day IF the End time is before 8.05am and thats was the end result

 

[Luke M]

Hi Moody,

Well, I admit I'm a little confused now as well, but I think it sounds like you've got it figured out...