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
I'm looking for a function similar to =WORKDAY but with a slight variation.
I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.
For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).
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.
View 4 Replies View RelatedBasically 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.
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.
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...
View 3 Replies View RelatedWe 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 ....
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.
View 5 Replies View RelatedI 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
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......)
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.
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.
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.
=IF($C2="","",IF(NETWORKDAYS($A2,$C2+1)
View 9 Replies View Related 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
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
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.
View 4 Replies View RelatedThe function is to show the next workday date in cell a1
I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$2-1,V2,Z2:Z$11)
P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.
Maybe I'm getting a blank, maybe the answer is so easy, but lately I canīt solve this: How do I use conditional format to change cell color if cell value (date) is not workday?
View 4 Replies View RelatedI have two dates in this format:
start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00
i need to check if the period in between those is during a workday.
our workday starts at 10:00:00 and finishes at 19:00:00
so my expected result is 3 hours. (1400-1100)
other example:
start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00
result is 1900-1100 + 1100-1000
= 9 hours
because it is during two workdays...
is there a way to calculate this in VBA?
This is probably a simple question, but one I can't figure out after several hours. I am creating a spreadsheet focused on certifications.
I need to take the date each initial certification was achieved, and add x years to each cert, which is simple enough.
The sticky part is that the (date + x years) can't logically be a weekend; if it is, I need to subtract one or two days from the result, so that it's the *weekday* immediately prior.
e.g. if my initial certification date is: 1/23/2006 and my certifcation expires in four 4 years:
A47= date(2006,1,23)
h47= 10
=DATE(YEAR(A47)+H47,MONTH(A47),DAY(A47))
Result = Saturday, January 23, 2010
If the result is a Saturday or Sunday, I need to subtract one or two days from the date. In this example, I'd like: Friday, January 22, 2010 as the result.
I've been playing with 'if', et. al., to no avail.
If I have a date in cell G6 of 10/7/08, how could I get the workday of 5 in cell H6? I tried the workday function, but have been unsucessful so far.
View 12 Replies View RelatedIm trying to display dates in excel sheet using macros. can anybody help me how to show Workday(x,x) in VBA.
View 7 Replies View RelatedI am having an issue, because aparently workday formula does not work in conditional formattings.
I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.
I'm using the following to return the previous TUESDAY in my Event Date cell F43
=$F$43-WEEKDAY($F$43+4)
It works great but if my event date is a Wednesday I need to return the Tuesday in the week prior not in the Tuesday immediately prior.
That is, If the event falls on Wed 22 May 2013 the cell needs to return Tuesday 14 May 2013 not Tuesday 21 May.
I need to be able to open a filenamed in the following format.
c:/workbook20060112.txt.
However the lsat part of the string is dyanmic and works with one business days lag (hence format sheet (date, "yyyymmdd") ) is not approriate. i also have the problem that that MS Excel does not have a holiday calendar (so this would not work over bank holidays/easte/exmas ect). The folder also contains archived files as below.
c:/workbook20060112.txt
c:/workbook20061130.txt
c:/workbook20061129.txt
I have been told about a filesearch object as well as a filesystem object. What situations would you use one over the other?
What I want it the most upto data file to pick up, but I dont want to do a loop where it look through all the files or something that counts down from today until it 'hits' the correct file.
I am trying to create a sheet for a project that will identify dates a project will conclude omitting weekends and holidays. I keep getting an error with the formula I have.
In my spreadsheet:
A1 is the start date
B1 is the number of days after the start date.
The formula I am using:
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{""4/6/2007","5/28/2007", "7/4/2007","9/3/2007","11/22/2007","12/25/2007","1/1/2008","1/21/2008","3/21/2008","5/26/2008", "7/4/2008","9/1/2008","11/27/2008","12/25/2008"}"}+0)
The error I get is with the parens (A1,A1+B1). - at least the parens are highlighted in purple.
I am trying to find a formula that would calculate what workday a date would be.
So if the date is 2/19/2007, I want to know that is the 13th workday.