# Adding Unique Days In A Range

Aug 4, 2008

We have a client that has to total the number of days that people received medical care (for insurance purposes). BUT...they can't count duplicate days. Some of the dates show a range of days, others only one day. They've asked for my help. (We're actually a computer services firm, but I'm pretty good with Excel...until now!)

In column C I have placed the correct totals for the date ranges, according to the payment policies. But I can't figure out how to write the formula that accommodates all the variables (mostly since all the unique dates aren't entered--only ranges).

Each line is a different medical charge, so line 1 might be 22 days of hospital stay. Line 2 is for a different charge, but covers the same dates, so it can't count. Lines 3, 4, and 5 all occur within the previously calculated range, so they don't count. The date range in line 6 is six days but 4 of them have been counted already, only 2 days fall outside an already calculated range.

It would be easy enough to do this manually for a small list, but this patient has 1367 entries in 4 months of care! And there are others that are much larger.

Fortunately, all the dates are in calendar order. Also, fortunately, they don't need line-by-line totals, just the grand total at the end of all the unique days in the range. So if I can figure out how many unique days are in the columns ....

## VBA - Finding And Counting Unique Dates And Days Of Week Within Date Range

Feb 11, 2013

Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.

For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.

## Adding 60 Days To A Date

Dec 5, 2011

I want to put a date in one cell then in the next cell i want it to add 60 days automatically, so to give the new date.

## Adding 7 Days To A Date Using Macro

Feb 25, 2009

Basically it's to make my content management easier.

I have made a button and assigned a macro to it. At the moment the button creates a new column and makes it the right size etc.

## Adding Up 45 Days Worth Of Money

Mar 4, 2007

Does anyone have a formula for calculating the amount of money used for a 45 day period.

e.g
01/02/07 \$13.00
05/02/07 \$22.00
10/03/08 \$6.00
etc etc

each amount for a given date added to give a total for 45 days only.

## Adding 7 Working Days To A Date

Dec 17, 2008

I have a worksheet where in column B I have a vendor name, column E I have a date value and in column AF I need to calculate a due date based on 7 working days for vendor A and 14 days (not working days) for vendor B. I've tried to write an IF statement but can't do it. Can anyone suggest the right combination of IF/AND statements that would do this? I attach the workbook should my useless attempt at explaining my problem be unclear...

Sep 16, 2006

I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.

## Workday - Adding Partial Days

Mar 31, 2007

When I use the formula below it works great;

= J10+(1/\$D\$10)

J10 = 3-1-07
D10 = 1.5 (Production Rate example .2,.5, 1.5 rate per day) The result is:

3-1-07
3-1-07
3-2-07
3-3-07
3-3-07
3-4-07
3-5-07 and so on...

however, when I try to include only workdays, it does not calculate correctly.

= WORKDAY(J10+(1/\$D\$10),1,Holidays!B75:B88) with the same definitions as above the result is...

3-1-07
3-2-07
3-3-07
3-4-07

Dec 8, 2008

I am looking for a formula that looks at a date and could add business days to it.

for example:

If the date in a field is Monday, 15-dec-08 and my formula is to add 5 Business days (mon-fri) to it not including that date. The desired result would be Monday, 22-dec-08 but my formula gives me 20-dec-08

## Automatically Adding Days Based Off Month

Oct 15, 2008

I am trying to automatically add the last day of the month based off of the month

for example

a1 = beginning date
a2 = end date

a1 will always be the 1st day of a given month

then based off a1, know the last day,(i.e.... october 1, then put 10-31-08 into a2, or november 1, then put 11-30-08......)

## Date In Cell - Adding Work Days

Nov 11, 2011

I have a date in A2 7th Nov 2011

Column B is number weeks until next appointment - 4.
Column C is the answer Friday 2nd December if the normal result is a Saturday or Sunday then use Friday.

## Adding Number Of Calendar Days To A Date

Apr 22, 2014

I'd like to add a number of calendar days to a date shown on Col A

I have 2 columns
a margin with a row number --- and Col A

When I try to add say 50 to the Col A ie =A261+50 to get 50 cd from cell A261 ----

I get a # problem

Tried to place 50 in a cell and then add the 2 cells but got the same result -- #

Can I use the margin row number to add to -- this would work well as I'd get the Row number which would also be the date.

## Adding Seconds And Minutes To Get Hours And Days

Nov 13, 2006

I am trying to add duration time for song files. 4 minutes and 32 seconds + 3 minutes 45 seconds, etc. etc. to get total hours and then to total to days. The range of cells is over 5,000 and I am trying to find out how long a playlist will play.

## Formula / Adding 2 Days To Saturday And Sunday For Count?

May 12, 2013

=IF(\$C2="","",IF(NETWORKDAYS(\$A2,\$C2+1)

## Adding Last 3 Days Hours Worked When A Date Is Entered

May 24, 2009

A B
1 4/1/09 12:15
2 4/2/09 10:00
3 4/4/09 8:10
4 4/6/09 9:00
5 4/8/09 5:00
6 4/11/09 7:00

I need to add up hours worked of last 3 days including current day. The date column does not contain everyday's date. How do I formulate a cell for date entry and another cell will show the sum of last 3 days including date not shown and date entered? E.g. If I enter date 4/6/09, result should be 17:10 hrs; 4/11/09, result should be 7:00.

## Adding Years, Days, Months To Date/time Format

Apr 26, 2007

I have some cells which must be in the format 15/06/2007 15:25

I then need to add either days, months or years onto it.

Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00

## Adding The Cells With Unique Values

Sep 29, 2009

can some one help me how to add the unique cell values using the formula bar?

like if on column C i would like to add all the row values which have the name "George" how can this be possible using the formula bar?

## Adding Values Against Unique Names

Apr 2, 2009

I have a spreadsheet with a series of names in a column and respective dollar values in a separate column. I would like a formula to automatically total the value spent against a particular name (without having to enter the name as a value each time).

Example

John Doe 3.89
John Doe 1.27
John Doe 0.98
Amy Smith 1.56
Amy Smith 2.29
Amy Smith 1.11
Alex Brown 0.67
Alex Brown 4.56
Alex Brown 8.87

I would like a formula to automatically populate a column, spreasheet, etc as follows:

John Doe 6.14
Amith Smith 4.96
Alex Brown 14.10

## Adding Unique Identifier To Rows Of Data

Jun 15, 2009

I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!

As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually

Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)

## Adding Prefix To Each Unique Entries In Cell

Jun 12, 2013

prefix.xlsx

I'm finding difficult to find add prefix to unique entries in the column.

For example:
82328331 is appearing 5 times in the column.
82328331A
82328331B
82328331C
82328331D
82328331E.

Likewise i need to add alphabetic series to all the values in the account number column. so i can create unique account number.

Find the attachment : Attachment 242493

## Adding Unique Items To ComboBox From Another File/Workbook

Aug 31, 2006

I need to add about ~200 items to Combo From Other Excel File (with no duplcates).
How to make it.

Private Sub UserForm_Activate()
With cboComp
End With
End Sub

## Runtime Error 1004 On Adding New Sheets Using Unique Values

Mar 5, 2014

I'm trying to set up a macro that will capture all the unique values in a column, and then create a new sheet for each value. What I've got so far technically works, but it's also adding an extraneous sheet (with a generic sheet name, not the specified name I'm giving the sheets) and also giving me "Run-time error '1004': Application-defined or object-defined error" when it runs.

## Excel 2007 :: Picture Created With Unique Names - Adding Comments To Cell

Oct 23, 2012

Excel 2007

I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"

Is there a way to reference the pictures I've created/named with my macro?

Here's the snippet of code that creates the pictures and names them:

Code:
For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Sheets("Allocation").Activate
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)

[Code] ......

Here are some examples that are close to what I'm looking for.

VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube

## Find Out How Many Ticks Were On Green Days, Amber Days And Red Days

Dec 19, 2008

I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.

## Count Days In Set Range Of Dates From A Range

Aug 7, 2014

Trying to count how many days from a set range of dates are within another range.

So for example, the first two dates, 7/29/2014 - 8/5/2014 would be 6

7/31/20148/6/2014 are the set dates

Count Days Within Range.xlsx

## Generate Specific Range Of Unique Random From Main Range

Feb 9, 2014

How to create a sheet to generate Random numbers from 2 specific list of a main list range of 36 Numbers. (List A = Specific 15 numbers, List B = The remaining Numbers from these 36)

Example :

-The Main List Range is from Number 01 to 36
-The Specific random list will be 15 Numbers of these 36

List A-. The Specific 15 Numbers are: 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33
List B-. Remaining Numbers : 04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36

So

I need to generate ONE Unique Random Numbers of List A ( 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33)
In Cell A1
In Cell A2
In Cell A3

And from List B, (04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36)

I need to do the same thing In
Cell A4
Cell A5
Cell A6

It's possible to have a 6 cells with random but Unique Digits? (not repeated numbers between the 6 cell ??)

## Average A Range IF Within 30 Days

Jun 5, 2008

I have a spreadsheet with dates running down column J, and a number running down column K. I want to average the numbers in column K for all the rows that are less than 30 days old.

I have a formula for determining if a date is less than a month old:
=IF(DATEDIF(J2,TODAY(),"d")<31,TRUE,FALSE)

How can I translate that into averaging a range?

I'm using Excel 2003

## Days So Far In A Date Range

Jan 7, 2010

I have a monthly start and end date e.g 2/01/2010 – 30/01/2010. I want to a formulate the below so I don’t have to do it manually each day. Start date is in E1 and end date E2

I have to change on another sheet how many Monday’s we have had so far in the month range and this is for each day of the working week (Mon, Tues,Wed,Thurs,Frid). So, so far this week we would have

(B11)Monday – 1
(E11)Tuseday - 1
(H11)Wednesday – 1
(K11)Thurdsay – 1
(N11)Friday – 0

By this time next week I would have

(B11)Monday – 2
(E11)Tuseday - 2
(H11)Wednesday – 2
(K11)Thurdsay – 2
(N11)Friday – 1

## Delete Range Every X Days

Jun 18, 2007

Find any code that deletes cell values in a range every 7 days.

cells.range("range").delete.currentdate + 7

every 7 days the cells in the range "range" get deleted etc

## Condittional Formatting: Range Of Days

May 29, 2009

Color cell AB3 in red if B3 shows the word OPEN and the date on AA is -1 day, -2 days, 0 day, 1day, 2 days... 50days from today (a range from -2 days to +50 days from the current date)