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 ....
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.
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...
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.
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
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.
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.
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.
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
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:
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)
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.
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 VBA Express : Excel - Add pictures that float like comments.
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.
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 ??)
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)
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
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)
