No of Days between two dates and offset formula

I want to find the no of days b/w two days it should consider saturday as 
half day holiday and sunday as fully day holiday . and any holidays in that 
week 

I need ans at c1= 11 . In between 1 and 15, date 14 is holiday.

      A                B                    C             D            E     
         F          G 

1   1-Jan-10      15-Jan-10


One more thing


In a1 = Project Start Month , b1= Jan-10
In a2 = Project Duration , b2= 10 ( which may change depend upon the project)


I need a formula so that from  it shows
a5=Jan-10
a6= Feb-10
a7= Mar-10   ...... so on depend upon the value in B2 cell 
0
Utf
1/4/2010 8:34:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1262 Views

Similar Articles

[PageSpeed] 2

Not sure I can help with needed formula in C1.  I think I could do it with a 
User Defined Function (VBA code), but someone may come along with an answer.

Meanwhile, for the formulas needed starting at A5, try this:
=IF(ROW()-ROW(A$5)+1<=B$2,DATE(YEAR(B$1),MONTH(B$2)+ROW()-ROW(A$5),DAY(B$1)),"")
Fill down the sheet as far as you care to go.  It will show a blank cell 
starting when you get beyond the # of months duration in B2.  The 'secret' is 
in the 
ROW() - ROW(A$5) parts.  ROW(A$5) is always going to evaluate to 5.  Since 
we are starting at row 5, current row - 5=0, when the formula moves to row 6, 
then (currentrow=6)-5 = 1 so we get the next month.
You could write it as 
=IF(ROW()-5+1<=B$2,DATE(YEAR(B$1),MONTH(B$2)+ROW()-5,DAY(B$1)),"")
But I think using the ROW(A$5) gives someone reading the formulas months 
from now a little clue as to what is going on - they should see that row 5 is 
the first row with the formula in it.





"vmohan1978" wrote:

> I want to find the no of days b/w two days it should consider saturday as 
> half day holiday and sunday as fully day holiday . and any holidays in that 
> week 
> 
> I need ans at c1= 11 . In between 1 and 15, date 14 is holiday.
> 
>       A                B                    C             D            E     
>          F          G 
> 
> 1   1-Jan-10      15-Jan-10
> 
> 
> One more thing
> 
> 
> In a1 = Project Start Month , b1= Jan-10
> In a2 = Project Duration , b2= 10 ( which may change depend upon the project)
> 
> 
> I need a formula so that from  it shows
> a5=Jan-10
> a6= Feb-10
> a7= Mar-10   ...... so on depend upon the value in B2 cell 
0
Utf
1/4/2010 9:56:01 PM
For the formula needed in C1, you could look at the NETWORKDAYS() function.  
This is part of an Add-In called the Analysis ToolPak.  You'll need to 
'install' that add-in if you haven't already to use NETWORKDAYS().  Search 
Excel Help for NETWORKDAYS() for all the details.

Basically NETWORKDAYS() gives you a total of the days between 2 days but 
automatically considers both Saturday and Sunday as non-work days.  So you 
would have an 'error' of 0.5 days per week.
With A1 = 1-Jan-2010 and B1 = 15-JAN-2010, then
=NETWORKDAYS(A1,B1) gives you a result of 11, as you asked for.  But I think 
that may be a 'special' case because of Jan 01.
If you enter dates of 1/4/2010 and 1/18/2010, then the result is 11, and I'm 
thinking that you really expect to see 12 in this case:  the formula has 
subtracted 2 whole days for the two Saturdays, but you only want to subtract 
2x0.5 or 1 day per 2 Saturdays.


"vmohan1978" wrote:

> I want to find the no of days b/w two days it should consider saturday as 
> half day holiday and sunday as fully day holiday . and any holidays in that 
> week 
> 
> I need ans at c1= 11 . In between 1 and 15, date 14 is holiday.
> 
>       A                B                    C             D            E     
>          F          G 
> 
> 1   1-Jan-10      15-Jan-10
> 
> 
> One more thing
> 
> 
> In a1 = Project Start Month , b1= Jan-10
> In a2 = Project Duration , b2= 10 ( which may change depend upon the project)
> 
> 
> I need a formula so that from  it shows
> a5=Jan-10
> a6= Feb-10
> a7= Mar-10   ...... so on depend upon the value in B2 cell 
0
Utf
1/4/2010 10:13:02 PM
Dear JLatham,
Thank you very much it worked Perfectly. It saved my time lot.


"JLatham" wrote:

> Not sure I can help with needed formula in C1.  I think I could do it with a 
> User Defined Function (VBA code), but someone may come along with an answer.
> 
> Meanwhile, for the formulas needed starting at A5, try this:
> =IF(ROW()-ROW(A$5)+1<=B$2,DATE(YEAR(B$1),MONTH(B$2)+ROW()-ROW(A$5),DAY(B$1)),"")
> Fill down the sheet as far as you care to go.  It will show a blank cell 
> starting when you get beyond the # of months duration in B2.  The 'secret' is 
> in the 
> ROW() - ROW(A$5) parts.  ROW(A$5) is always going to evaluate to 5.  Since 
> we are starting at row 5, current row - 5=0, when the formula moves to row 6, 
> then (currentrow=6)-5 = 1 so we get the next month.
> You could write it as 
> =IF(ROW()-5+1<=B$2,DATE(YEAR(B$1),MONTH(B$2)+ROW()-5,DAY(B$1)),"")
> But I think using the ROW(A$5) gives someone reading the formulas months 
> from now a little clue as to what is going on - they should see that row 5 is 
> the first row with the formula in it.
> 
> 
> 
> 
> 
> "vmohan1978" wrote:
> 
> > I want to find the no of days b/w two days it should consider saturday as 
> > half day holiday and sunday as fully day holiday . and any holidays in that 
> > week 
> > 
> > I need ans at c1= 11 . In between 1 and 15, date 14 is holiday.
> > 
> >       A                B                    C             D            E     
> >          F          G 
> > 
> > 1   1-Jan-10      15-Jan-10
> > 
> > 
> > One more thing
> > 
> > 
> > In a1 = Project Start Month , b1= Jan-10
> > In a2 = Project Duration , b2= 10 ( which may change depend upon the project)
> > 
> > 
> > I need a formula so that from  it shows
> > a5=Jan-10
> > a6= Feb-10
> > a7= Mar-10   ...... so on depend upon the value in B2 cell 
0
Utf
1/5/2010 4:55:01 AM
Hi JLatham,
Thank you very much.

I tried the networkdays() formula but it consider both sat and sun as 
holiday . but i require sat as half day.



"JLatham" wrote:

> For the formula needed in C1, you could look at the NETWORKDAYS() function.  
> This is part of an Add-In called the Analysis ToolPak.  You'll need to 
> 'install' that add-in if you haven't already to use NETWORKDAYS().  Search 
> Excel Help for NETWORKDAYS() for all the details.
> 
> Basically NETWORKDAYS() gives you a total of the days between 2 days but 
> automatically considers both Saturday and Sunday as non-work days.  So you 
> would have an 'error' of 0.5 days per week.
> With A1 = 1-Jan-2010 and B1 = 15-JAN-2010, then
> =NETWORKDAYS(A1,B1) gives you a result of 11, as you asked for.  But I think 
> that may be a 'special' case because of Jan 01.
> If you enter dates of 1/4/2010 and 1/18/2010, then the result is 11, and I'm 
> thinking that you really expect to see 12 in this case:  the formula has 
> subtracted 2 whole days for the two Saturdays, but you only want to subtract 
> 2x0.5 or 1 day per 2 Saturdays.
> 
> 
> "vmohan1978" wrote:
> 
> > I want to find the no of days b/w two days it should consider saturday as 
> > half day holiday and sunday as fully day holiday . and any holidays in that 
> > week 
> > 
> > I need ans at c1= 11 . In between 1 and 15, date 14 is holiday.
> > 
> >       A                B                    C             D            E     
> >          F          G 
> > 
> > 1   1-Jan-10      15-Jan-10
> > 
> > 
> > One more thing
> > 
> > 
> > In a1 = Project Start Month , b1= Jan-10
> > In a2 = Project Duration , b2= 10 ( which may change depend upon the project)
> > 
> > 
> > I need a formula so that from  it shows
> > a5=Jan-10
> > a6= Feb-10
> > a7= Mar-10   ...... so on depend upon the value in B2 cell 
0
Utf
1/5/2010 4:57:01 AM
Reply:

Similar Artilces:

Can I change the calendar year beginning date?
Our fiscal year runs July 1 thru June 30 of each year. I need to track attendance, gas cards, etc for monthly reports. I have a HUGE table with a gazillion queries and reports for each one. I need one report to reflect quarterly output for our fiscal year as stated above. Can I make this report do that? Thank you! Create a query to use as the source for your report. In query design, type this into the Field row: FinYear: DateAdd("m", -6, [InvoiceDate]) replacing InvoiceDate with the name of your date field. This yields 2007 for all dates in the 2007/2008 financial year...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days for a feild with a date in it [Sold Date]. Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31 Jan 07. I did =[Sold date]+30 and it did not work Thanks how about dateadd: dateadd("d", 30, [Sold Date]) -- steve. "KAnoe" <KAnoe@discussions.microsoft.com> wrote in message news:DE9BDDAE-5C84-4A52-8185-DAB6C3167E7A@microsoft.com... >I have a feild that I would like to show what the date would be in 30 days > for a feild with a date in it [Sold Date]. > ...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

passeord Protect for excel formula and VBA code
Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

Tiebreaker in a Index formula?
I need a tiebreaker for a formula. Right now the formula reads: =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there is a tie it shows on one store twice instead of listing the two separate stores. I have two stores have the exact scores and it list the store with the lower store number twice. For example I have store 598 and store 698 both with a score of 100% but in the ranking of the stores it shows store 598 twice instead of 598 then 698. Is it possible to have a tiebreaker, with the index formula, that can list the stores in descending order, 598 first...