Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling data by start/finish dates | Excel Discussion (Misc queries) | |||
Formulating Different Start/Finish Dates with Man Hours | New Users to Excel | |||
start finish dates | Excel Discussion (Misc queries) | |||
Getting no of hours from start to finish | Excel Discussion (Misc queries) | |||
How do I chart date ranges with varying start and finish dates? | Charts and Charting in Excel |