CODEWITHSUNDEEP
google-sheets
AdeP
AdeP

Reputation: 53

Strange formula behaviour

I hope someone can help with this one. First some background.

This is a link to a cut-down version of the spreadsheet I'm having problems with. Set to view only but you can copy it to play with.

https://docs.google.com/spreadsheets/d/1RRhvC3wk4FK5Hcb2LatK62sguS95ssxqGPHsnNQRDRM/edit?usp=sharing

The purpose of the sheet is to keep track of workers' start and end times for each day they are working, and show totals for each day, The sheet (Planner) is divided into departments/sections. Column C and rows 4 & 5 are hidden in normal use.

Looking at the first week, 15/1/2017, the totals after section 1 are all correct as expected. The line Total Early, shows the count for all workers who have a start time either on or before the time shown in the corresponding column in row 4. The line Total Late, shows the count for all workers who have an end time that is either on or after the time shown in the corresponding column in row 5. The line Total Day, shows a count for how many workers are in on that day regardless of when they start and finish.

Looking at the second week, 22/1/2017, this is where is starts to get interesting. The counts for the Sunday, Monday, Thursday, Friday and Saturday are correct. The Total Late count for Tuesday and Wednesday is including the text entries in the count. This happens with several of the choices from the validation box, but not all. Strange!

Looking at the week commencing 10/12/2017, it gets even more interesting! The Total Early count is totally screwed up showing 0 (zero) and the Total Late count even more so, showing 9, which just happens to be the number of cells in the range, Its the same formula, so why the different behaviour?!

The formula used in these cells has gone through many iterations but with the same or similar results. I'm totally stumped on this one so any help anyone can give would be gratefully received.

Let me know if you have questions or need more info. Thanks.

Upvotes: 2

Views: 51

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

Counting some Text entries as late

This is due to the way how you are determining whether someone is late or not.

=MID(Q14, 7, 5) yields y. Pretty much any programming language will interpret alphabetical characters as being "greater" than numbers, ergo y > 20:00.

An easy way to circumvent this is to extract the start-end times safely and then run the comparison. 

=ArrayFormula(SUM(--(IFERROR(REGEXEXTRACT(Q7:Q16, "(\d\d:\d\d)$"), "")>$Q$5))))

Messed up counts in December

Here your time is actually an Excel timestamp, if you change the display to Number you'll see 0.38 and 0.88

This is because the extending opening times in Data are formatted as timestamps. Just change the format to text and you're good, the other way would be to parse the opening and closing times as timestamps.

Upvotes: 1

Related Questions