LinkBack URL
About LinkBacks
i have a worksheet where i track batches start and end times based on the amount of time each takes
ex
batch # start end
580 0:00 12:30 [B]MON[B]
581 12:30 01:00 How can it get this area to show TU based on the time change to a new day?
i have a worksheet where i track batches start and end times based on the amount of time each takes
ex
batch # start end
580 0:00 12:30 [B]MON[B]
581 12:30 01:00 How can it get this area to show TU based on the time change to a new day?
Hi randomhavok
Would it not be easier if you included the date!
In the example given the date HASN'T changed.
0:00 12:30 is Mon
12:30 1:00 is still Mon since the greatest time in a day is 23:59:59, 00:00:00 would be the next day.
You could try something along the lines of
IF(B2<A2, "newday","sameday")
where B1 and A1 are times as youve suggested
Then you just gotta work out how to add 1 to the existing day.
I agree with Kevin UK, adding a date would make life simpler
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
If times are in B2 and C2 perhaps you could use this formula in D2 (assuming previous weekday like "Mon" in D1)
=TEXT(INDEX({1,2,3,4,5,6,7},MATCH(D1,TEXT({1,2,3,4,5,6,7},"ddd"),0)+(C2<B2)),"ddd")
Audere est facere
it works to change to Tue, Wed etc but sometimes the batches run into the same day how can i get where it will keep Mon if less then 23:59?
The idea is that D2 will keep the same day as D1 as long as B2 < C2 so if B2 = 17:00 and C2 = 23:00 then the day will stay the same......but if B2 = 21:00 and C2 = 06:00 then D2 will be a day after D1, isn't that what you wanted?
If not then please post a sample workbook with some representative sample data and an indication of the required results
i have included a sample and manually entered the days for the desired results
usually with go with m, t, w, th but if excel will not accept i can change to mon, tue etc.
You have been a great help
thank you
Are we talking 7 days a week, e.g. including sa and su or do you want f to go straight to m?
95% of the time it is monday to friday.
worst case i can manually add the saturday when necessary
thanks
OK, assuming the first day is entered manually in E1 and is either m, t, w, th or f then try this formula in E2 copied down
=INDEX({"m","t","w","th","f","m","t"},MATCH(E1,{"m","t","w","th","f"},0)+(MOD(B2,1)<MOD(C1,1))+(MOD(C2,1)<MOD(B2,1)))
Get a bit complicated now, just to show the day of the week!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks