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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
438
HighAndWilder
H
T
  • Solved
Stop and start counting in different cells
Replies
9
Views
164
Turbos
T
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
127
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
418
bobsan42
bobsan42

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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