Excel wizardry please!!

I'd start with looking at CountIF. Although someone else will be along shortly with an easy way to do it

manual way - sort by time column, count the rows.

"There must be a simple way to count this up! I can’t get a pivot to do it coz there’s so many different individual times!?"

Are the dates and times stored as text or formatted properly as dates and times?

The manual way is how I would normally go about it, but as a learning exercise for future, there must be something better!

I think they're stored as text, how can I tell!?

They appear DD/mm/yyyy in date column, hh:mm:ss in time - but do you mean like a standardized excel date format rather than just the numbers transposed from the source?

You mean the time values are in a separate column?

Either way, I'd use Excels "Text to Column" function to split out the time HH values into their own column and then just filter out anything greater than 08 and less than 16...

Are the dates and times stored as text or formatted properly as dates and times?

The easiest way to do this and see what is going on is to add an IF in the next column that labels the sample "DAY" or "OVERNIGHT" and then countif the overnights. You can probably make a more complex countif to do it all at once but its harder to unpick errors then.

e.g.

Col A = Sample ID
Col B = Date / Time of receipt

(I've assume Col B is a proper Excel date time)

Add Col C and in C2 put:

= IF ( OR ( hour(B2) < 8 , hour(B2) > 16), "Overnight", "Day")

fill that down column C.

Now you can use =countif(C2:C4000, "Overnight")

If you want to group by Date, or day of the week etc you'll need to do more work potentially using a pivot table (but adding another column just for date).

EDIT - I wrote that before I saw your post that Col B is date and Col C is time.  Potentially can still use same approach.  if its text you'll need to extra the hour first (say =left(C2,2))...

"I think they’re stored as text, how can I tell!?"

Select the column then Ctrl 1 to bring up the format cells dialogue (or select it from the ribbon) then choose date/time and the required format. If they're stored as text you'll get all sorts of bother and I think pivot filtering should work better if they're correctly formatted (don't have Excel handy at the moment)

I think they’re stored as text, how can I tell!?

They appear DD/mm/yyyy in date column, hh:mm:ss in time – but do you mean like a standardized excel date format rather than just the numbers transposed from the source?

Right click on the cell > format cells and then see what it comes up with.
I had to completely rebuild a spreadsheet a few months back when I "inherited" it from a work colleague and found that he'd just done everything manually and the date column was a total mess of manually inputted dates, different formats (dd/mm/yy, dd/mm/yyyy, mm/yyyy....), bits where Excel had tried to guess it and auto-format...

Once it's in a standard format and you've actually told Excel to use that format and that one only, there should be options for conditional formatting ("IF x = 08:00 - 16:00, yes" type equation) and from there ways to do a COUNTIF so anything that comes back with YES (this is between 08:00 and 16:00), gets counted as receipted that day.

If your time column is C then in the next empty column:

=COUNTIF(OR(C1<8/24,C1>16/24)

Then drag the formula down to the bottom of the timestamps.

Under the last cell do a sum of the cells above.

Pivot table it, put the time as the filter
change the sum to count.
?

back at it today - got the time and date columns set to the correct format now, they were just text. This is the gist of what it looks like, there's 4000 rows :

spec number     date                            time

123                        23/11/23                  21:24:00

124                        23/11/23                  22:25:00

125                        24/11/23                  00:36:00

I tried this =COUNTIF(OR(C1<8/24,C1>16/24) but it says too few arguments - not sure what to do with that also tried conditional formatting to change cell if value between 08:00:00 and 16:00:00 but that didn't do owt!

so need to get a count of many arrive overnight on each day

so day 1 = 20 samples, day 2 = 32 samples, day 3 = 30 samples etc

Right add another column with a simple IF that says “DAy” or “NIGHt” and then run a pivot on that.

you could use left / right / mid ie a1 - "24/11/23 08:36:00" use mid
= mid(A1, 10,2) will give you the hour ie extract from 10th character for 2 characters, ie 08 in this case.
if you convert the column to a date
then you could use =year(a1)&text(month(a1),"00") to give format, 202311 for example.. (yyyymm are then always 6 characters) could do same for days =day(a1) , =hour(a1) etc

may be best to split out the time entirely in a new column, using mid function    =mid(a1,10,8)

A1  =HOUR(A1)  [ADDED TO COLUMN B] THEN

=IF(b1<8,"Night",IF(b1>18,"Night","Day"))

then create a pivot, i hate the new style pivots that group dates, i'll always created a month column as above, easier to sort data

I tried the formula you suggested higher up but no joy, = IF ( OR ( hour(B2) < 8 , hour(B2) > 16), “Overnight”, “Day”)

Should i concatenate the two columns into a single date/time?

edit - can't even make it do that tbh! it's converting the date into a weird format :

23/11/2023     21:24:00 ---> 4525321:24:00

I tried this =COUNTIF(OR(C1<8/24,C1>16/24) but it says too few arguments

Now that I'm at my laptop with Excel open in front of me that formula won't work. No idea why, but there you go.

Playing about with things you may need to do some data cleansing first. Excel reads dates as whole numbers and times as decimals, but it's not quite that simple, e.g if you're asking it to format cells to specifically display times only then Excel would read the number 0.5 as 12:00, but it would also read the number 1.5 as 12:00

To make it easy, in a new column, (e.g. in D1) enter: =C1-INT(C1)

and copy/drag down to the bottom of the list - this will remove any whole numbers and leave just a decimal between 0 and 1

Then in the next column enter: =IF(OR(D1<8/24,D1>16/24),1,0)

and copy/drag down to the bottom of the list

and then at the bottom of the column do a sum of the above cells

=IF(AND( HOUR(A8)>=8,HOUR(A8)<16),"Day","Night")

This will get you a filter for in scope or not and put that in column B say.. Se

Then id' put a row of days somewhere Say D1 downwards with progressive dates on. and put this in column E

=Countifs(B:B,"Night",A:A,">="&D1,A:A,"<"&D2 )
Where B is where your day/night formula is and A is where your date field is and D is where your table of dates is

The countifs has three things its checking for. 1 is it day or night, 2 is the date greater than your search date, and 3 its less than tomorrows date (or whatever interval you put in, say weeks, or month etc)

Maybe a layout like this. (changed my formula to match)

A B C D
spec number date time

123 23/11/23 21:24:00 IF(AND( HOUR(C2)>=8,HOUR(C2)<16),”Day”,”Night”)

124 23/11/23 22:25:00 IF(AND( HOUR(C3)>=8,HOUR(C3)<16),”Day”,”Night”)

125 24/11/23 00:36:00

Else where

F G

23/11/23 Countifs(D:D,”Night”,B:B,”>=”&F1,A:A,”<“&F2 )
24/11/23 Countifs(D:D,”Night”,B:B,”>=”&F2,A:A,”<“&F3 )
25/11/23
26/11/23

ps. to avoid excel you'd be best to ask the source of this data to amend his/her sql code to write it in, is thats an internal team it'll take 15-20 mins by someone competent, if IT involved it may cost budgets and if its outsourced they'll want £10-20k for the amendment and then take 200 days to deliver it :0)

I tried the formula you suggested higher up but no joy, = IF ( OR ( hour(B2) < 8 , hour(B2) > 16), “Overnight”, “Day”)

(you can use AND and switch everything around as also suggested above)

Once you've done that a Pivot table, or event a pivot graph is the way forward, so you can summarise the count of day/night on each date, probably by month etc too.
Not knowing what you are doing - you may also find it useful to know =weekday(b2) will give you the day of the week (check I think Sun = 1, Mon =2 etc). That may show some interesting trends about samples on days of the week.

Pivot table would be simplest unless I am missing something?

dunno if this has been solved yet, but due to general unfamiliarity with Excel whenever I get a problem like this I just write a script to handle it. On Mac you can use AppleScript or Javascript, think on PC you can use JS or VBScript?

You can even get ChatGPT to code the script for you these days. Probably take 2 mins 😂

Where c2 is the first cell with a time in it, use:

=If(or(c2<8/24,c2>16/24),"Night","Day")

And copy it down
That will say "night" or "day" against each row.

The countif wasn't working because you need a comma between the cell(s) you're counting in and the criteria you're looking for. But rather than using COUNTIF and OR I'd just use COUNTIFS.

I'm afraid I'm still getting problems with all these formulas!

 you’d be best to ask the source of this data to amend his/her sql code to write it in

that's me!

it’ll take 15-20 mins by someone competent,

😳

I've taken the simplistic route and sorted by time, block deleted out the day time samples and

Pivot table would be simplest unless I am missing something?

done a pivot. Got me some usable numbers now, it's sort of inaccurate because a night time goes over two dates, but we're still accounting for the numbers received "out of hours" - 23 on average if you're interested, we're taking this base number and will then way over-compensate from it.

Still wan to get this cracked though really, I would like to improve my Excel prowess 🤣

Try adding a New Col with this formula =IF(AND(A1<TIMEVALUE("16:00:00"),A1>TIMEVALUE("08:00:00")),"Day","Night") - Where A1 is your correctly formatted Time value...

PowerQuery handles time, and tell you where you have errors - but it can be a little tricky to get used to.
If you want to use your existing data in Excel you’ll have to convert it to a table (select all - ctrl+t) and load the data from there (Data Ribbon - from table range).
Change the time column to the data type time, using the selector at the top left of the column.
I’d then add a conditional column, with 2 ifs: an if before 08:00:00 and if after 16:00:00 - both then 1, else 0.
Load these to a Pivot Table report and do a sum of the 1s from your new column by date and you’ll have your out of hours numbers.
If you want a count of both then use Day and Night instead of 1 and 0, and use these are your pivot table columns and count spec num as values.
RM.

My hunch is that you need to add a couple of cells at the bottom that contain 8:00 and 16:00. The problem being that you are your countif and or functions are trying to compare the time in hours to decimal time

These cells will then contain the decimal time.

Then when you do your countif instead of entering the time as 8:00 and 16:00 add reference to the cells containing the time. These will need to absolute cell references with dollar signs. Or you could mainly enter the decimal numbers into the logical functions

Formula in column C is a nested if:
=IF(HOUR(A8)>=16,"Night",IF(HOUR(A8)<8,"Morning","Day"))

Formula in column F
=COUNTIFS(A:A,">"&E15,A:A,"<"&E16,C:C,"Morning")+COUNTIFS(A:A,">"&E14,A:A,"<"&E15,C:C,"Night")

(nothing in columns B and D)

A B C D E F
23/02/2024 08:00 Day
23/02/2024 09:00 Day
24/02/2024 10:00 Day
25/02/2024 09:00 Day
26/02/2024 09:00 Day
27/02/2024 09:00 Day
28/02/2024 09:00 Day
29/02/2024 09:00 Day 01/03/2024 00:00 0
01/03/2024 09:00 Day 02/03/2024 00:00 0
02/03/2024 09:00 Day 03/03/2024 00:00 0
03/03/2024 19:00 Night 04/03/2024 00:00 2
04/03/2024 06:00 Morning 05/03/2024 00:00 0
05/03/2024 09:00 Day
06/03/2024 09:00 Day
07/03/2024 09:00 Day

I don’t know why you are still struggling - what you are doing is not that hard. The format of times etc is messy but it’s easily understood with some quick googling etc. you’ve not told us what’s wrong - the trick to “debugging” excel formulae is to break them down into very small chunks and understand the errors.

done a pivot. Got me some usable numbers now, it’s sort of inaccurate because a night time goes over two dates,

Still wan to get this cracked though really, I would like to improve my Excel prowess

As I say in every Excel thread - the question you should really ask - is Excel the right tool for the job?

Is this a one-off or a regular thing?

I can probably make a mobile app to display the data in the way you want it if you like - but that's probably not helpful!

(I say Mobile, but it would also work on a desktop)

Try using Microsoft Copilot (via Bing) for this. I copied and pasted in your post and it spat out the answer, and you can tweak it by plain English conversation.

==========

Yes, you can do this by using a combination of Excel functions and features. Here's a step-by-step guide:

1. **Add a new column to your data that will indicate whether the sample was received overnight.**
- In the first cell of the new column (let's say it's column D), enter the following formula: =IF((C2>=TIME(16,0,0))+(C2<TIME(8,0,0)), "Overnight", "Daytime"). This formula checks if the time in cell C2 is greater than or equal to 16:00 (4:00 PM) or less than 8:00 (8:00 AM). If it is, it returns "Overnight", otherwise, it returns "Daytime". - Replace C2 with the cell that contains the first time value in your data. - Drag the fill handle (the small square at the bottom-right of the cell) down to copy this formula for all the rows in your data. 2. **Create a PivotTable to count the number of samples received overnight each day.** - Go to Insert > PivotTable.
- In the Create PivotTable dialog box, select the range of your data (including the new column) and choose where to place the PivotTable.
- In the PivotTable Field List, drag the "Date of Receipt" field to the Rows area, the "Overnight" field to the Columns area, and the "Sample ID" field to the Values area.
- In the Values area, click on the dropdown arrow next to "Count of Sample ID", select "Value Field Settings", and choose "Count" to count the number of samples.
- The PivotTable will show the count of samples received during the daytime and overnight for each date. You can filter the "Overnight" column to show only the overnight samples.

Remember to replace the column letters and cell references in the steps above with the ones that match your data.