Count cells below certain times

[JMWh1t3]

Hiya,

I have a workbook that I drop data into on a daily basis and I've got an issue getting a count to work for a time column.

The column is just times, C:C, in order but they change thought the day depending on the workload done. So for instance right now the times range from 13:15 past midnight and past 24 hours to 15:15. (All times are 00,15,30,45 past the hour but not every hour)

What I need to count is all the cells from the first time up to the next instance of 06:00 (but not past this as there could be up to 3 days worth of data at some points.

 

[etaf]

can you provide an example

cells from the first time up to the next instance of 06:00

you say maybe
3 days worth of data at some points.

You could have 06:00 in the list 3 times
Not sure how excel knows - " the first time up "

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[JMWh1t3]

can you provide an example

you say maybe
3 days worth of data at some points.

You could have 06:00 in the list 3 times
Not sure how excel knows - " the first time up "

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thank you, I will update the post with a sample sheet when I am back at the computer.

 

[JMWh1t3]

can you provide an example

you say maybe
3 days worth of data at some points.

You could have 06:00 in the list 3 times
Not sure how excel knows - " the first time up "

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

See sample sheet.

Loading Google Sheets

 

[etaf]

thanks
you have for 1 day times that are lunchtime , then 18:00 then next day
but the day of the week is 1

12:00
12:15
12:15
18:00
18:00
0:15
0:30

is the sample a true representation of the real data ALSO do you JUST want to count for the 1st set of data - so DAY1 = based on 1st character of release number
OR is this something you want to be able to specify the day and count

I'm assuming you only ever have 7 days worth of data at the most

will the release Number always be ascending sequential ????

i' struggling at the moment to see a solution , as anything , like max/min wont wont work - because of the previous day times (not really previous days - Just looks like that with the order )

Day2
is that count 56

 

[JMWh1t3]

thanks
you have for 1 day times that are lunchtime , then 18:00 then next day
but the day of the week is 1

12:00
12:15
12:15
18:00
18:00
0:15
0:30

is the sample a true representation of the real data ALSO do you JUST want to count for the 1st set of data - so DAY1 = based on 1st character of release number
OR is this something you want to be able to specify the day and count

I'm assuming you only ever have 7 days worth of data at the most

will the release Number always be ascending sequential ????

i' struggling at the moment to see a solution , as anything , like max/min wont wont work - because of the previous day times (not really previous days - Just looks like that with the order )

Day2
is that count 56

Hiya,

Yes, true representation, and always ascending sequential and always 4 digits.

Ideally just want a count from the current day up to 6am the next working morning.

Usually the most on there is 4 days if there is weekend releases, in which case I would need a count of Friday (sat & sun if there is any) and up to Monday 6am.

Yes, day 2 count is 56

 

[etaf]

Ideally just want a count from the current day up to 6am the next working morning.

its the next day in theory - BUT the data - doesn't have dates and the reference just shows 1 - so from the data same day , and so not a way to say next day - if it did have the date in the file or a true way to see its the next day - it would be straight forward

as shown in reference starting 2 , it starts with times form lunchtime and then changes day at reference 2106 - but nothing to show a change of day


now we can count if time under 06:00
But not sure how to do the day before ..........


perhaps i'm thinking this all wrong , and somehow use that reference number

you could have
3001 - 22:00
3002 - 02:00
3003 - 07:35

and that would be 2

but i'm not sure of a solution