Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 516
Default Returning $ values by start & finish dates only

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas = 25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G, H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10 07-11
14-11
2 EWP 100 100 700 29-10 13-11 400
700 700
3 Fork 100 100 700 05-11 10-11 0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in E2,
F2, E3 and F3. Any help would be appreciated. Thanks.

--
Regards
Matt
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 105
Default Returning $ values by start & finish dates only

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas = 25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G, H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10 07-11
14-11
2 EWP 100 100 700 29-10 13-11 400
700 700
3 Fork 100 100 700 05-11 10-11 0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in E2,
F2, E3 and F3. Any help would be appreciated. Thanks.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 516
Default Returning $ values by start & finish dates only

Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2 and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas = 25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G, H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10 07-11
14-11
2 EWP 100 100 700 29-10 13-11 400
700 700
3 Fork 100 100 700 05-11 10-11 0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in E2,
F2, E3 and F3. Any help would be appreciated. Thanks.




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Returning $ values by start & finish dates only

Rather than changing a semi-colon to a colon, try changing it to a comma.
(Semi-colon and comma are the alternatives for a list separator, depending
on the regional settings).
--
David Biddulph

"Matt" wrote in message
...
Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2
and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas =
25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G,
H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10
07-11
14-11
2 EWP 100 100 700 29-10 13-11
400
700 700
3 Fork 100 100 700 05-11 10-11
0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in
E2,
F2, E3 and F3. Any help would be appreciated. Thanks.






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 516
Default Returning $ values by start & finish dates only

Thanks David but that doesn't fix it either. I think there is another issue
too where cell B2 isn't referenced anywhere within the formula. Any help
would be great.
--
Regards
Matt


"David Biddulph" wrote:

Rather than changing a semi-colon to a colon, try changing it to a comma.
(Semi-colon and comma are the alternatives for a list separator, depending
on the regional settings).
--
David Biddulph

"Matt" wrote in message
...
Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2
and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas =
25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G,
H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10
07-11
14-11
2 EWP 100 100 700 29-10 13-11
400
700 700
3 Fork 100 100 700 05-11 10-11
0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in
E2,
F2, E3 and F3. Any help would be appreciated. Thanks.








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 105
Default Returning $ values by start & finish dates only

Try :
=(G1-$E$2<8)*$B$2+((MIN(G1,$F$2)-MAX(F1,$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
Have a look at :
http://www.filedropper.com/returning...ishdatesonly_1
Daniel
Matt a formulé la demande :
Thanks David but that doesn't fix it either. I think there is another issue
too where cell B2 isn't referenced anywhere within the formula. Any help
would be great.
--
Regards
Matt


"David Biddulph" wrote:

Rather than changing a semi-colon to a colon, try changing it to a comma.
(Semi-colon and comma are the alternatives for a list separator, depending
on the regional settings).
--
David Biddulph

"Matt" wrote in message
...
Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2
and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas =
25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G,
H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10
07-11
14-11
2 EWP 100 100 700 29-10 13-11
400
700 700
3 Fork 100 100 700 05-11 10-11
0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in
E2,
F2, E3 and F3. Any help would be appreciated. Thanks.








  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 516
Default Returning $ values by start & finish dates only

Still doesn't work Daniel. Works OK for the columns as per my 1st post but
when you add extra dates in the columns to the right and then extend the
formula across, it returns wrong results. Results should equal 0 for all
additional columns. Any thoughts?
--
Regards
Matt


"Daniel.C" wrote:

Try :
=(G1-$E$2<8)*$B$2+((MIN(G1,$F$2)-MAX(F1,$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
Have a look at :
http://www.filedropper.com/returning...ishdatesonly_1
Daniel
Matt a formulé la demande :
Thanks David but that doesn't fix it either. I think there is another issue
too where cell B2 isn't referenced anywhere within the formula. Any help
would be great.
--
Regards
Matt


"David Biddulph" wrote:

Rather than changing a semi-colon to a colon, try changing it to a comma.
(Semi-colon and comma are the alternatives for a list separator, depending
on the regional settings).
--
David Biddulph

"Matt" wrote in message
...
Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2
and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas =
25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G,
H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10
07-11
14-11
2 EWP 100 100 700 29-10 13-11
400
700 700
3 Fork 100 100 700 05-11 10-11
0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in
E2,
F2, E3 and F3. Any help would be appreciated. Thanks.









  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 105
Default Returning $ values by start & finish dates only

Well it does work but you have to change the demob date.
Daniel

Still doesn't work Daniel. Works OK for the columns as per my 1st post but
when you add extra dates in the columns to the right and then extend the
formula across, it returns wrong results. Results should equal 0 for all
additional columns. Any thoughts?
--
Regards
Matt


"Daniel.C" wrote:

Try :
=(G1-$E$2<8)*$B$2+((MIN(G1,$F$2)-MAX(F1,$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
Have a look at :
http://www.filedropper.com/returning...ishdatesonly_1
Daniel
Matt a formulé la demande :
Thanks David but that doesn't fix it either. I think there is another
issue too where cell B2 isn't referenced anywhere within the formula. Any
help would be great.
--
Regards
Matt


"David Biddulph" wrote:

Rather than changing a semi-colon to a colon, try changing it to a comma.
(Semi-colon and comma are the alternatives for a list separator, depending
on the regional settings).
--
David Biddulph

"Matt" wrote in message
...
Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2
and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas =
25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G,
H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10
07-11
14-11
2 EWP 100 100 700 29-10 13-11
400
700 700
3 Fork 100 100 700 05-11 10-11
0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in
E2,
F2, E3 and F3. Any help would be appreciated. Thanks.











  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 516
Default Returning $ values by start & finish dates only

Daniel
I have worked out the required formula.

=IF(G$1-$E2=0,IF(G$1-$E2<7,$B2,0),0)+IF(G$1-$E2=0,IF(G$1-$E2<7,((G$1-$E2)+1)*((1/7)*$D2),0),0)+IF(G$1-$E27,IF($F2-G$10,(G$1-F$1)*((1/7)*$D2),0),0)+IF(G$1-$F2=0,IF(G$1-$F2<7,($F2-F$1)*((1/7)*$D2),0),0)+(IF(G$1-$F2=0,IF(G$1-$F2<7,$C2,0),0))

When this formula is pasted into cell G2 and copied across to H2, I2, J2 etc
it will add mob, demob and weekly costs as applicable depending on the start
and finish dates.
--
Regards
Matt


"Daniel.C" wrote:

Well it does work but you have to change the demob date.
Daniel

Still doesn't work Daniel. Works OK for the columns as per my 1st post but
when you add extra dates in the columns to the right and then extend the
formula across, it returns wrong results. Results should equal 0 for all
additional columns. Any thoughts?
--
Regards
Matt


"Daniel.C" wrote:

Try :
=(G1-$E$2<8)*$B$2+((MIN(G1,$F$2)-MAX(F1,$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
Have a look at :
http://www.filedropper.com/returning...ishdatesonly_1
Daniel
Matt a formulé la demande :
Thanks David but that doesn't fix it either. I think there is another
issue too where cell B2 isn't referenced anywhere within the formula. Any
help would be great.
--
Regards
Matt


"David Biddulph" wrote:

Rather than changing a semi-colon to a colon, try changing it to a comma.
(Semi-colon and comma are the alternatives for a list separator, depending
on the regional settings).
--
David Biddulph

"Matt" wrote in message
...
Thanks for the response Daniel.
Unfortunately it doesn't work. I copied and pasted your formula into G2
and
hit enter. Received error message and proposed correction changed ; to :
within your formula. I accepted this change and it still didn't work. I
have tried to solve this myself but to no avail. Any ideas?
--
Regards
Matt


"Daniel.C" wrote:

In cell G2 :
=(G1-$E$2<8)*$C$2+((MIN(G1;$F$2)-MAX(F1;$E$2-1))*$D$2/7)+(G1$F$2)*$C$2
to be pasted in H2, I2 and dragged downward as far as needed.
Regards.
Daniel

Aim: Calculate weekly cost of all equipment using mobilisation (mob),
demobilisation (demob) and weekly rates.

Source data: Mob and demob dates only.

My spreadsheet currently looks like below (Date format eg Xmas =
25-12-08).
Dates shown are for end of week (Friday). Costs tally up in columns G,
H and
I.

A B C D E F
G H I
1 Equip $/Mob $/Demob $/Wk Mob Demob 31-10
07-11
14-11
2 EWP 100 100 700 29-10 13-11
400
700 700
3 Fork 100 100 700 05-11 10-11
0
400 400
4
Totals 400 1100 1100

G3 = $100/mob + 3 days (3/7 x $700 = $300) = $400 total

I am looking for a formula to insert into G2, H2, I2, G3, H3 and I3 to
achieve the results shown. Formula needs to reference date entries in
E2,
F2, E3 and F3. Any help would be appreciated. Thanks.












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pulling data by start/finish dates Carla Excel Discussion (Misc queries) 6 May 17th 08 02:12 PM
Formulating Different Start/Finish Dates with Man Hours DBaddorf New Users to Excel 5 May 12th 08 01:37 PM
start finish dates JB2010 Excel Discussion (Misc queries) 2 September 19th 07 01:46 PM
Getting no of hours from start to finish Peter Mount Excel Discussion (Misc queries) 3 September 10th 06 02:11 PM
How do I chart date ranges with varying start and finish dates? projectplanner Charts and Charting in Excel 4 May 1st 05 11:36 PM


All times are GMT +1. The time now is 10:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"