Stop calculating after a certain date

**lara5555** · 12-06-2011, 05:33 AM

Hi,
I am trying to put together a plant register that calculates the costs from on hire and off hire dates. I am using IF function, which does calculate the cost between the two dates and stops accumulating in the to date total cost, however I also have a weekly cost which I want to return a result of 0 if there is an off hire result and this exceeds Todays date so it does not accumulate in the weekly total. I cant delete this line as then the to date total cost will be in correct.

As I send out the plant register every Friday I want this cost included up to the friday of the week off hired. Once this has been exceeded then I want this to return a 0 result to it does not contribute to the weekly cost.

Therefor I am looking for this formulae;-
If start date is more than todays date then return 0. If off hire date is entered then include cost until the Friday of the off hire week. Once Friday of that week has passed then put 0 to stop counting.

I have a formula which automatically calculates the next Friday, but this changes as its based on todays date, where as I want it fixed from the off hire date =TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,4,5,6,7,1)

Any help would be great :-)

Thanks

**arthurbr** · 12-06-2011, 07:11 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

**lara5555** · 12-06-2011, 08:07 AM

Hi,

Please see attached, I have put in different senarios that would occure, eg;-
1) Start date is 09-12-11, so calculation has not yet started
2) Off hire date is today, so cost to date has stopped calculating, however the weekly total is returning a value which is ok up until this Friday 08-12-11.
3) Off hire date was 02-12-11, however the weekly rate is still being counted in the weekly plant total which is what I am trying to stop.
4) Transport charges (adds first transport charge to cost to date, when start date is more than or equal to today, and then adds to return transport charge when off hire date is inserted).

Many thanks

**Fotis1991** · 12-06-2011, 10:25 AM

Hi Lara,

I give you a solution, using IF(OR) & weeknum functions and a helper column.

Hope to helps you.

Regards

**lara5555** · 12-06-2011, 12:26 PM

Hi Fortis,

Many thanks for your spreadsheet, I have changed some of the formulaes as it returned a 0 in the total to date cost which I wanted to keep hence not deleting the line, however it now works so thats great - thank you so much :-)

**Fotis1991** · 12-06-2011, 12:53 PM

You are welcome.

pLS MARK THE THREAD AS solved.

**lara5555** · 12-08-2011, 07:30 AM

How do I mark it as solved?

**Fotis1991** · 12-08-2011, 08:18 AM

Hi Lara

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click

Also

If any member's response has helped to solve your problem, please click the star icon below of their post to show your appreciation

**lara5555** · 12-20-2011, 09:53 AM

Ok Many thanks, will bear this in mind for the future.

However I have a slight problem with the formula now that we are going into the new year.

Because I cant delete any lines even if they have been off hired as they contribute to the total cost to date, and I want the weekly cost to calculate up until the Friday of the off hire week, but then return a 0 value so it does not continue calculating in the weekly total thereafter, the formula is linked to the week number. However when we get in to the new year the week number resets its self, so anything off hired in week 40 then resets itself as its done to calculate until it is greater than, it starts to cost in the weekly total again.

Hopefully this makes sense.

Any help would be great.

Many thanks

**Fotis1991** · 12-20-2011, 10:39 AM

Hi again.

Maybe, it will not be necessary to modified the formulas.

Pls take a look to the attachment and tell me if the result in ROW 16, IS CORECT.

If no, pls tell me which is the corect result??

**lara5555** · 12-20-2011, 12:25 PM

Hi Fortis,

Row 16 is correct, however if you change it so that it was hired and off hired last year, eg hired 01-10-11, and off hired 31-10-11 then the formula doesnt work as it starts calculating in the weekly total column again.

**Fotis1991** · 12-20-2011, 01:24 PM

So here we are...again...

(My name is Fotis. IS it so difficult??)

First of all, for making your tests, you have to change the date in F3. To be not TODAY(), as your attachment, but let's say>>20/12/11 for testing this year and ...>>31/01/12, for testing next year..

As you can understand it's not easy for me to describe this but pls take a look to the attachment.

I think that adding two more(helper)columns and modifying the formula in Weekly TTL, column, the result is ok.

Hope to helps you.

**lara5555** · 12-22-2011, 06:14 AM

Sorry about the incorrect spelling Fotis !

I cant seem to open the worksheet. Are you able to reattach it?

Thanks

**Fotis1991** · 12-22-2011, 06:21 AM

Good morning Lara

Sure..Here it is...

**lara5555** · 12-22-2011, 09:25 AM

Hi Fotis,

Please see attached spreadsheet. I think this now works? I have changed the week number column to weekending so that it generates a friday date, and then the weekly total calculates only if todays date is equal to or less than the weekending date of the off hire date!

Hopefully that make sense, I have attached spreadsheet anyway. Do you see any errors?

Thanks

**Fotis1991** · 12-22-2011, 10:22 AM

Very good iob Lara. Very good.

I think this is it...!

If this thread, wasn't yours, i would gιve you a *!!

The only that i have to tell you(if you want to), change in the formulas the F3 cell, to TODAY(). Just to be more pretty...!

**lara5555** · 12-22-2011, 01:56 PM

Thanks Fotis, I thought I had set it up that way, but must have forgotten when checking the formula worked. Thank you for all your help.

Happy Christmas and New Year

**Fotis1991** · 12-23-2011, 04:10 AM

Glad to helped you, Lara

Happy Christmas and New Year, for you. too!!

Stop calculating after a certain date

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Re: Stop calculating after a certain date

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions