Countif for parts Night shift (count passes over to Next Day)

[DaleJ92]

Morning All,

I received some great help regarding a Countif issue and solved my problem however i need some guidance with a more complex issue.

I am trying to create a document that shows parts produced on the current shift (now), if you see my attached document you will understand how i have done it.

it all worked perfectly until it come to testing the night shift, because the night shift crosses over to another day im not sure how to write the formulae.

If anyone could take a look and point me in the write direction, il be really grateful

Kind regards

Dale

[Pete_UK]

You should change your formula in K6 of the SHIFT sheet to this:

=TEXT($I$3+1,"dd/mm/yyyy ")&TEXT(D6,"hh:mm:ss")

and make similar changes to the other night shift cells.

That being said, you still get zero in the LIVESCREEN sheet, as there is no data which matches.

Hope this helps.

Pete

[Roel Jongman]

two problems..

1. you are using text formulas for dates that will not work. since the dates in the livedata are true dates (values)
2. all you need to do for the night shift is make sure that the endtime is greater then the start times.
you do that by adding 8/24 to the time 22:00 so in D6 you do C6+8/24 that will still show as 6:00 in timeformat while decimal value is 1,33

In the J and K range all you do is a simple add frmula like =$I$3+C4 etc.. to get combination of date and time

Remember dates and times are nothing more then numbers with a formatting mask to show as dates. a full day = 1 an hour is 1/24= 0,0466667 you can simply add and substract frm a startdate and time to get to an end date and time.

see attachment, changes only made to the shifts sheet
and one small change in scrap % on livescreen to show 0% instead of #### if there is no correct percentage

[DaleJ92]

Morning Both,

Many thanks for your replies, i understand what your saying about using the +1 function but im struggling to understand how it would work for my issue.

I am using the =Today() function to ensure the data counted is from the shift happening live, this works perfectly for the Early and late shifts.

However the night shift will display today(10PM) to tommorow(6AM) (using +1) while it is before 12, when it passes to the next day after 12 the =Today() function
means the Data shown will be today(10PM) to tommorow(6AM), when really it would need to be Yesterday (10PM) to today (6am).

I hope i have explained what i mean, im guessing i need some sort of IF function to use one code 22:00-00:00 and the other code 00:00-06:00, depending on the time on computer display.

But as for how i would write this function i have no clue haha.

Many thanks in advance.

Kind regards

[Roel Jongman]

your problems were caused by you using text functions unneeded and in the wrong way to get the proper end time.

have you opened my file?
that does not only give you a clue on how to solve
it is a working solution.. livescreen now shows the totals you expect also for he night shift

just look at the SHIFTS tab at cell D6 for the formula that is inthere and the new formulas in col J and K on how start and end are calculated

[DaleJ92]

Afternoon Jongman,

Yes i looked at your file but the issue still remains, because the data will displayed live, when the clock ticks over to the next day
(while the night shift is part way through). because of this the =Today Function, will then update and make the code start looking at the next day.

For instance the night shift starts on 03/04/19 22:00

Capture1.PNG

Then when the night shift runs past 00:00 and the Date changes to 04/04/19, the Dates update also due to the =today() Function.

Capture2.PNG

So when i still need it to be looking at all the parts made in the current shift 03/04/2019 (22:00) >>> 04/04/2019 (06:00),
After 12:00 it is only showing 04/04/2019 (22:00) >>> 05/04/2019, which means it basically shows 0 because its in the future, so no parts made.

I hope you understand what im saying its difficult to explain, but this data will be used live and will be open in the middle of the night on shift.

Many thanks for your help.

Kind regards

Dale

[Roel Jongman]

Ah, ok, I missed the part of it being live updated.. sorry..

Basicly at midnight you want the date to stay unchanged until the nightshift ends. (6 AM)

Try if it works with the formula

Please Login or Register  to view this content.

NOW() will give current date and time, the MOD around it makes sure only the time part is evaluated.
and the IF test makes sure that as long as the time is before 6 AM (or the value in cell D6) that the date is changed to yesterdays date

In D6 I put back your original value of 6:00 for this formula
Also I changed the formula in the Nightshift end times to =I3+1+D6

now with the new formula in I3 (and simular functions for other shifts) the date should only change after 6:00

I also uploaded a new sheet as seeing it in the sheet makes easier to understand.
you can test the effect of the delayed date change by typing an end time in D6 that is after current time, then you will see the date jumping back in the first shift block

[DaleJ92]

Morning Roel,

Its difficult to explain a problem sometimes because im not that good with excel.

Anyways, many thanks for your help this seems to be working exactly how i want.

The only issue i see from testing is when the Computer clock passes past the End time of the night shift the dates dont automatically change,

i have to go on the cell and re-enter the same time they update. Could i solve this by refreshing the Sheet somehow?

Kind regards

Dale J

[Roel Jongman]

Well since you said it was a livestream I was assuming that the data on Livedata was inserted at certain intervals and that at that time the sheet also automaticly recalculates. the today() function is a volatile function that will recalculate itself everytime something is changed or updated in the sheet. But if that is not the case than you may need to add some step to the process that imports new data to livedata to trigger the whole workbook to recalculate.