Count the number of days.

W

wkirkcrawford

Board Regular
Joined
Dec 7, 2013
Messages
72
Greetings,

I keep a diabetic log that is usually 6 months or so. One insulin pen last 28 days and I have a column that I note starting a new pen. I usually have don’t use it all by the 28th day. Therefore, I need to replace that pen.

Today, I’m taking that pen twice a day. Usually, I run out before the 28th day now. But not always.

So, I need a way to flag me when I need to replace it. Therefore, I assume it would be something using Conditional Formatting.

W. Kirk Crawford
Tularosa, New Mexico
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Hello WKirkCrawford

I'm not sure if this is what you are needing or not, but maybe it will get you started in the right direction. I assume your data starts in row A2. First, I formatted the 'A' column as DATE. You can pick which option you prefer. Then in cell 'A2', I entered today's date as 2019/2/4, the date you started using a new pen. The second thing I'm assuming is that you want to order a new pen a few days before your old one is finished. If the old one lasts 28 days, I randomly picked 25 as when you'd want to order a new one, which would give you a three day advance warning. You can adjust that number to suit your needs. Using my examples, cell 'B2' would show March 1, 2019 as 25 days after you started using that pen.

In cell 'B2' I entered the following formula and copied/pasted it down the column.

Code: 
[COLOR=black][FONT=Calibri]=IF(A2="","",A2+25)[/FONT][/COLOR]

I hope this helps.

TotallyConfused
 
Last edited:
Upvote 0
Totally,

It's a start, but I'm trying to insert a snapshot or screen dump of what I'm talking about. Can't seem to get it to work.

The column when I get a new pen, I put an asterisk on that date.



W. Kirk Crawford
Tularosa, New Mexico
 
Last edited:
Upvote 0
Re: Count the number of days. Link added

I've add most of my log.
Where's the red asterisk, it the day I started using a new PEN.
I want to know when it's time to start using a new pen after 28 days.

W. Kirk Crawford

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1301"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]All Readings[/TD]
[TD="colspan: 3"]Breakfast[/TD]
[TD="colspan: 5"]Lunch[/TD]
[TD="colspan: 5"]Dinner[/TD]
[TD="colspan: 2"]Before Snack[/TD]
[TD="colspan: 2"]Sugar Drops[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]255[/TD]
[TD]Avg. time between the last shot.[/TD]
[TD]High[/TD]
[TD]172[/TD]
[TD][/TD]
[TD]Avg. time between the last shot.[/TD]
[TD]High[/TD]
[TD]193[/TD]
[TD]New Pen[/TD]
[TD] [/TD]
[TD]Avg. time between the last shot.[/TD]
[TD]High[/TD]
[TD]255[/TD]
[TD]Humlog[/TD]
[TD]High[/TD]
[TD]199[/TD]
[TD]High[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]71[/TD]
[TD]Low[/TD]
[TD]108[/TD]
[TD][/TD]
[TD]Low[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]Low[/TD]
[TD]110[/TD]
[TD]Low[/TD]
[TD]94[/TD]
[TD]Low[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD]151[/TD]
[TD]Average[/TD]
[TD]145[/TD]
[TD][/TD]
[TD]Average[/TD]
[TD]127[/TD]
[TD] [/TD]
[TD]Average[/TD]
[TD]190[/TD]
[TD]Average[/TD]
[TD]148[/TD]
[TD]Average[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]Median[/TD]
[TD]150[/TD]
[TD]Median[/TD]
[TD]149[/TD]
[TD][/TD]
[TD]Median[/TD]
[TD]122[/TD]
[TD] [/TD]
[TD]Median[/TD]
[TD]183[/TD]
[TD]Median[/TD]
[TD]151[/TD]
[TD]Median[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]Date:[/TD]
[TD]Morning[/TD]
[TD]13:36[/TD]
[TD]Reading[/TD]
[TD]Units[/TD]
[TD]Afternoon[/TD]
[TD]5:33[/TD]
[TD]Reading[/TD]
[TD]Units[/TD]
[TD]Dinner[/TD]
[TD]4:46[/TD]
[TD]Reading[/TD]
[TD]Units[/TD]
[TD]Units[/TD]
[TD]Time[/TD]
[TD]Reading[/TD]
[TD]Time[/TD]
[TD]Reading[/TD]
[/TR]
[TR]
[TD]1/16/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:10 PM[/TD]
[TD][/TD]
[TD]98[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]4:00 PM[/TD]
[TD]3:49[/TD]
[TD]182[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]7:56 PM[/TD]
[TD]171[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/17/2019[/TD]
[TD]5:42 AM[/TD]
[TD]13:42[/TD]
[TD]129[/TD]
[TD]24[/TD]
[TD]11:01 AM[/TD]
[TD]5:19[/TD]
[TD]104[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]4:02 PM[/TD]
[TD]5:01[/TD]
[TD]209[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]7:59 PM[/TD]
[TD]147[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/18/2019[/TD]
[TD]5:14 AM[/TD]
[TD]13:11[/TD]
[TD]131[/TD]
[TD]24[/TD]
[TD]11:02 AM[/TD]
[TD]5:47[/TD]
[TD]132[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:01 PM[/TD]
[TD]4:59[/TD]
[TD]222[/TD]
[TD]18[/TD]
[TD]10[/TD]
[TD]7:59 PM[/TD]
[TD]132[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/19/2019[/TD]
[TD]6:14 AM[/TD]
[TD]14:12[/TD]
[TD]108[/TD]
[TD]24[/TD]
[TD]11:06 AM[/TD]
[TD]4:51[/TD]
[TD]188[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:20 PM[/TD]
[TD]5:14[/TD]
[TD]216[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]8:04 PM[/TD]
[TD]167[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/20/2019[/TD]
[TD]6:15 AM[/TD]
[TD]13:54[/TD]
[TD]146[/TD]
[TD]24[/TD]
[TD]11:56 AM[/TD]
[TD]5:40[/TD]
[TD]116[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:08 PM[/TD]
[TD]4:12[/TD]
[TD]191[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]8:02 PM[/TD]
[TD]119[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/21/2019[/TD]
[TD]6:05 AM[/TD]
[TD]13:57[/TD]
[TD]151[/TD]
[TD]24[/TD]
[TD]11:03 AM[/TD]
[TD]4:57[/TD]
[TD]97[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]4:02 PM[/TD]
[TD]4:59[/TD]
[TD]177[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]8:01 PM[/TD]
[TD]147[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/22/2019[/TD]
[TD]5:36 AM[/TD]
[TD]13:33[/TD]
[TD]149[/TD]
[TD]24[/TD]
[TD]11:09 AM[/TD]
[TD]5:33[/TD]
[TD]111[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]4:06 PM[/TD]
[TD]4:56[/TD]
[TD]159[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:05 PM[/TD]
[TD]127[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/23/2019[/TD]
[TD]6:00 AM[/TD]
[TD]13:54[/TD]
[TD]155[/TD]
[TD]24[/TD]
[TD]11:05 AM[/TD]
[TD]5:05[/TD]
[TD]122[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:01 PM[/TD]
[TD]4:56[/TD]
[TD]239[/TD]
[TD]18[/TD]
[TD]11[/TD]
[TD]8:00 PM[/TD]
[TD]166[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/24/2019[/TD]
[TD]5:54 AM[/TD]
[TD]13:52[/TD]
[TD]157[/TD]
[TD]24[/TD]
[TD]11:01 AM[/TD]
[TD]5:06[/TD]
[TD]137[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:11 PM[/TD]
[TD]5:10[/TD]
[TD]110[/TD]
[TD]18[/TD]
[TD]7[/TD]
[TD]8:02 PM[/TD]
[TD]103[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/25/2019[/TD]
[TD]6:29 AM[/TD]
[TD]14:18[/TD]
[TD]130[/TD]
[TD]24[/TD]
[TD]11:09 AM[/TD]
[TD]4:39[/TD]
[TD]124[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:14 PM[/TD]
[TD]5:04[/TD]
[TD]170[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:01 PM[/TD]
[TD]157[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/26/2019[/TD]
[TD]5:54 AM[/TD]
[TD]13:40[/TD]
[TD]128[/TD]
[TD]24[/TD]
[TD]11:07 AM[/TD]
[TD]5:13[/TD]
[TD]130[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:12 PM[/TD]
[TD]5:04[/TD]
[TD]226[/TD]
[TD]18[/TD]
[TD]10[/TD]
[TD]8:01 PM[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/27/2019[/TD]
[TD]5:43 AM[/TD]
[TD]13:31[/TD]
[TD]128[/TD]
[TD]24[/TD]
[TD]11:59 AM[/TD]
[TD]6:16[/TD]
[TD]100[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:05 PM[/TD]
[TD]4:05[/TD]
[TD]255[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]8:02 PM[/TD]
[TD]123[/TD]
[TD]12:33 PM[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]1/28/2019[/TD]
[TD]5:49 AM[/TD]
[TD]13:44[/TD]
[TD]151[/TD]
[TD]24[/TD]
[TD]11:06 AM[/TD]
[TD]5:16[/TD]
[TD]147[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:02 PM[/TD]
[TD]4:55[/TD]
[TD]168[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:02 PM[/TD]
[TD]168[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/29/2019[/TD]
[TD]5:22 AM[/TD]
[TD]13:20[/TD]
[TD]150[/TD]
[TD]24[/TD]
[TD]11:01 AM[/TD]
[TD]5:38[/TD]
[TD]118[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]4:01 PM[/TD]
[TD]5:00[/TD]
[TD]192[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]7:58 PM[/TD]
[TD]94[/TD]
[TD]7:21 AM[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]1/30/2019[/TD]
[TD]5:51 AM[/TD]
[TD]13:50[/TD]
[TD]161[/TD]
[TD]24[/TD]
[TD]11:06 AM[/TD]
[TD]5:14[/TD]
[TD]132[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:13 PM[/TD]
[TD]5:07[/TD]
[TD]163[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:00 PM[/TD]
[TD]192[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/31/2019[/TD]
[TD]5:35 AM[/TD]
[TD]13:21[/TD]
[TD]144[/TD]
[TD]24[/TD]
[TD]11:09 AM[/TD]
[TD]5:34[/TD]
[TD]129[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:03 PM[/TD]
[TD]4:54[/TD]
[TD]153[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:04 PM[/TD]
[TD]175[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]5:39 AM[/TD]
[TD]13:36[/TD]
[TD]172[/TD]
[TD]24[/TD]
[TD]11:03 AM[/TD]
[TD]5:23[/TD]
[TD]85[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]4:16 PM[/TD]
[TD]5:13[/TD]
[TD]226[/TD]
[TD]18[/TD]
[TD]10[/TD]
[TD]7:57 PM[/TD]
[TD]131[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/2/2019[/TD]
[TD]5:02 AM[/TD]
[TD]12:45[/TD]
[TD]157[/TD]
[TD]24[/TD]
[TD]1:31 PM[/TD]
[TD]8:28[/TD]
[TD]120[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]5:33 PM[/TD]
[TD]4:02[/TD]
[TD]243[/TD]
[TD]18[/TD]
[TD]10[/TD]
[TD]7:58 PM[/TD]
[TD]146[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/3/2019[/TD]
[TD]5:53 AM[/TD]
[TD]12:20[/TD]
[TD]171[/TD]
[TD]24[/TD]
[TD]12:11 PM[/TD]
[TD]6:17[/TD]
[TD]101[/TD]
[TD]7[/TD]
[TD]*[/TD]
[TD]4:06 PM[/TD]
[TD]3:54[/TD]
[TD]190[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]8:07 PM[/TD]
[TD]199[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/4/2019[/TD]
[TD]5:16 AM[/TD]
[TD]13:10[/TD]
[TD]149[/TD]
[TD]24[/TD]
[TD]11:01 AM[/TD]
[TD]5:45[/TD]
[TD]193[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:02 PM[/TD]
[TD]5:01[/TD]
[TD]162[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:02 PM[/TD]
[TD]168[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/5/2019[/TD]
[TD]5:35 AM[/TD]
[TD]13:32[/TD]
[TD]146[/TD]
[TD]24[/TD]
[TD]11:07 AM[/TD]
[TD]5:32[/TD]
[TD]121[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]3:06 PM[/TD]
[TD]3:58[/TD]
[TD]181[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]10:19 PM[/TD]
[TD]151[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/6/2019[/TD]
[TD]6:13 AM[/TD]
[TD]15:06[/TD]
[TD]172[/TD]
[TD]24[/TD]
[TD]11:02 AM[/TD]
[TD]4:49[/TD]
[TD]138[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]4:02 PM[/TD]
[TD]4:59[/TD]
[TD]158[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]8:00 PM[/TD]
[TD]164[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/7/2019[/TD]
[TD]5:12 AM[/TD]
[TD]13:10[/TD]
[TD]119[/TD]
[TD]24[/TD]
[TD]11:04 AM[/TD]
[TD]5:51[/TD]
[TD]174[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]4:02 PM[/TD]
[TD]4:58[/TD]
[TD]183[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]8:01 PM[/TD]
[TD]154[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/8/2019[/TD]
[TD]5:17 AM[/TD]
[TD]13:14[/TD]
[TD]123[/TD]
[TD]24[/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][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Count the number of days. Link added

Sandy666,

What's so complicated about it?

W. Kirk Crawford
Tularosa, New Mexico
 
Upvote 0
Re: Count the number of days. Link added

Hi,

If I understand correctly...

You would need to replace the "Pen" when it's depleted or on the 28th day (maybe 29th day), whichever comes first, is that correct ?

It's kind of difficult to follow your Table as shown above, probably easy for you since you created it.

Which column shows, How many times the "Pen" has been used since last replaced, and the date it was last replaced?

Also, need to know, How many times can a "Pen" be used, and whether it expires on or after the 28th day.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Formula to Number Multi-Part Transactions and Re-Start Count Each Day
Replies
6
Views
265
turn7or5
T
S
  • Question Question
Count coloured cells in a range which has been conditionally formatted
Replies
1
Views
626
awoohaw
awoohaw
G
  • Solved
Conditional format based on number of days over stated date
2
Replies
11
Views
933
awoohaw
awoohaw
H
  • Solved
Insert blank rows conditionally
Replies
3
Views
510
kevin9999
K
Nlhicks
  • Question Question
Conditional Format to turn Red if 5 yrs out, Yellow if 4 yrs out and green otherwise
2
Replies
13
Views
311
etaf
etaf

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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