Count the number of days.

[wkirkcrawford]

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 28^th day. Therefore, I need to replace that pen.

Today, I’m taking that pen twice a day. Usually, I run out before the 28^th 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

 

[TotallyConfused]

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.

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

I hope this helps.

TotallyConfused

 

[wkirkcrawford]

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

 

[wkirkcrawford]

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]

 

[sandy666]

Re: Count the number of days. Link added

complicated

 

[wkirkcrawford]

Re: Count the number of days. Link added

Sandy666,

What's so complicated about it?

W. Kirk Crawford
Tularosa, New Mexico

 

[jtakw]

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.

 

[sandy666]

Re: Count the number of days. Link added

Sandy666,
What's so complicated about it?

Ops, I was not talking about the problem but about the table

 

[wkirkcrawford]

Re: Count the number of days. Link added

Sandy666,

Would you like the file?

W. Kirk Crawford
Tularosa, New Mexico

 

[sandy666]

Re: Count the number of days. Link added

Would you like the file?

No thanks, I've my own