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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

N
  • Question Question
CountIFS, conditional formatting and ignoring hidden columns
Replies
0
Views
124
NewbieExcel123
N
T
  • Solved
Formula to Number Multi-Part Transactions and Re-Start Count Each Day
Replies
6
Views
290
turn7or5
T
S
  • Question Question
Count coloured cells in a range which has been conditionally formatted
Replies
1
Views
661
awoohaw
awoohaw
G
  • Solved
Conditional format based on number of days over stated date
2
Replies
11
Views
941
awoohaw
awoohaw
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
520
Fluff
Fluff

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