Timestamp Day Count

A

aidanwhite

New Member
Joined
Oct 3, 2017
Messages
12
Hi, I've currently got a load of timestamps in column J, generated by pressing a button. I wondered if anyone would be able to tell me how to get cell G10 to turn green if 3 or more of the timestamps are in the same day? Thanks!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Assuming you mean the same date?

Try the following as formula for the CF =sumproduct(--int(J1:J10))>=3
 
Last edited:
Upvote 0
01/09/2017 09:30
01/09/2017 19:30
02/09/2017 05:30 01/09/2017 2
02/09/2017 15:30 02/09/2017 2
03/09/2017 01:30 03/09/2017 3
03/09/2017 11:30 04/09/2017 2
03/09/2017 21:30 05/09/2017 3
04/09/2017 07:30 06/09/2017 2
04/09/2017 17:30 07/09/2017 2
05/09/2017 03:30 08/09/2017 3
05/09/2017 13:30 09/09/2017 2
05/09/2017 23:30 10/09/2017 1
06/09/2017 09:30 11/09/2017
06/09/2017 19:30
07/09/2017 05:30
07/09/2017 15:30
08/09/2017 01:30 easy to count how many each 24 hour period
08/09/2017 11:30
08/09/2017 21:30 then lookup each date against the new table
09/09/2017 07:30 and if 3 or more apply conditional formatting
09/09/2017 17:30
10/09/2017 03:30
in A1 it would be

=VLOOKUP(A1,mytable,2)>2
 
Upvote 0
Thanks arthurbr

I tried this but the cell turns green as soon as one value is entered into row J, but I'd need the colour change after three in the same day.

Any ideas?
 
Last edited:
Upvote 0
Hi oldbrewer,

I'm not quite sure how the '=VLOOKUP(A1,mytable,2)>2' applied in conditional formatting works.

No change occurs when data is added to my timestamp list in column J?
 
Upvote 0
it goes to the table returns a number, and if that is 3 or more it turns the cell red or green or pink or blue or yellow....
 
Upvote 0
I'm not quite sure you understand what I mean, oldbrewer.

The list of dates and times descend in row J. I'd like a formula that searches these dates, and then turns a separate cell (G10) green if 3 or more of the dates are the same.

I think you're trying to turn the 3 cells with the same date green - I'd like a cell separate to change colour.
 
Upvote 0
so if the second column in post 3 contains any number 3 or greater, you can color which ever cell you want
 
Upvote 0
[TABLE="width: 914"]
<colgroup><col><col><col span="11"></colgroup><tbody>[TR]
[TD="align: right"]01/09/2017 09:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2017 19:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/09/2017 05:30[/TD]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="colspan: 3"]formula giving 3 in cell F3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/09/2017 15:30[/TD]
[TD="align: right"]02/09/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2017 01:30[/TD]
[TD="align: right"]03/09/2017[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=SUMPRODUCT((C2:C12>2)*1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2017 11:30[/TD]
[TD="align: right"]04/09/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2017 21:30[/TD]
[TD="align: right"]05/09/2017[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2017 07:30[/TD]
[TD="align: right"]06/09/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so if f3 is greater than 0 color a specific cell[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2017 17:30[/TD]
[TD="align: right"]07/09/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2017 03:30[/TD]
[TD="align: right"]08/09/2017[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the cond formatting formula in that cell will be[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2017 13:30[/TD]
[TD="align: right"]09/09/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2017 23:30[/TD]
[TD="align: right"]10/09/2017[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=F3>0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2017 09:30[/TD]
[TD="align: right"]11/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2017 19:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/09/2017 05:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/09/2017 15:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/09/2017 01:30[/TD]
[TD="colspan: 4"]easy to count how many each 24 hour period[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/09/2017 11:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/09/2017 21:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/09/2017 07:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/09/2017 17:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2017 03:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Calculating difference between Timestamps
Replies
1
Views
433
HighAndWilder
H
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
113
Nicha
N
T
  • Solved
Can Excel script be triggered by a cell change?
Replies
2
Views
2K
tonywatsonhelp
T
D
  • Question Question
VBA optimization advice
Replies
3
Views
381
bobsan42
bobsan42
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
129
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,577
Messages
6,173,163
Members
452,503
Latest member
AM74

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top