Subtracting dates,
Posted by Jim on May 04, 2001 9:09 PM
I have a column of dates in S5 thru S26 and i have a
row of numbers J3 thru R3, i need to create a new
row of dates based on the rows in column S minus the
numbers in row 3, -EXAMPLE-
J3 K3 L3 M3 N3 O3 P3 Q3 R3 S3 S4 S5
65 70 50 58 30 30 15 25 25
=(S4-J3)/ =(S4-K3) 5/04/01
=(S5-J3)/ =(S5-K3) 7/01/01
I hope this make some sense, Jim
Posted by Kevin James on May 04, 2001 9:26 PM
Subtracting dates
Jim,
The example you gave all ran together and I am having trouble deciphering it.
Based on what I think you mean, here my answer
The intersecting cell of rows (number of days to subtract) and columns (dates) you need the formula for that scenario. Right?
If so, let me over-simplify for example's sake:
A2: 5/1/2001
A3: 6/3/3001
B1: 10
C1: 20
D1: 30
B2: $A2-B$1
Copy that formula through rest of the adjoining cells and it will be correct for the grid of A1 through D3.
Posted by Jim on May 04, 2001 10:08 PM
Re: Subtracting dates
Thanks Kevin, I left out 1 important issue, when subtracting the days it can't count sat sun or holidays
Jim
Posted by Dave Hawley on May 04, 2001 10:20 PM
Hi Jim
Based on your example, try this formula in cell J4
=INDIRECT(ADDRESS(COLUMN()-5,19))-J3
...and copy along to R4
Dave
OzGrid Business Applications
Posted by Kevin James on May 04, 2001 10:39 PM
Still something missing
Hi Jim,
Based on your reply, you've still left even another stone yet unturned....
If you don't want to count weekends then which way do you want to calculation to go? Up or down one date?
Posted by Jim on May 04, 2001 10:46 PM
Re: Still something missing
Kevin your on you toes, i would need it to be
"down"
Thanks Jim
Posted by Kevin James on May 04, 2001 10:47 PM
Thread for research. Msg: 16491
Posted by Kevin James on May 04, 2001 10:51 PM
See above reference to thread
--
Posted by Dave Hawley on May 05, 2001 3:23 AM
Hi Jim
Just read your email and I'm still not sure what you really mean. However I have included a formula that will give you the result i think you want. Put this in cell J4 and copy down and across. The "Holiday" is a named range for you to place in Public holidays. The NETWORKDAYS Function will exclude Week-ends
=INDIRECT(ADDRESS(ROW(),19))-NETWORKDAYS(INDIRECT(ADDRESS(ROW(),19))-J$3,$S4,Holidays)
Dave
OzGrid Business Applications
Posted by Jim on May 05, 2001 5:18 PM
Thanks Dave
Thanks Dave:
I apologize for not being more clear on the
subject. You're as good at deciphering garbled up
questions as you are at Excel.
Jim
