Calculating days and month

[LasseJ]

Dear all

Can someone help me out with this one?:

I need to calculate days and month based on two fields: 'Starting date' and 'End date', so it adds up to a number. The number should be calculated as follows: Basic fee (268 $), 532 $ pr extra month from starting date and finally 19 $ pr extra days

Ex.

'Starting Date': July 28. 'End Date': September 30.

- Basic fee: 268 $

- July 28 til 27 September: 2*532$ = 1.064 $

- 28. september till 30. september 3*19 $ = 57 $

Total: 1389 $

[comment]

Try =

Let ( [

end = EndDate + 1 ;

m = 12 * ( Year ( end ) - Year ( StartDate ) ) + Month ( end ) - Month ( StartDate ) - ( Day ( end ) < Day ( StartDate ) ) ;

d = end - Date ( Month ( StartDate ) + m ; Day ( StartDate ) ; Year ( StartDate ) )

] ;

268 + m * 532 + d * 19

)

Note that it's not good practice to store data (prices) in calculation formulae.

Edited November 2, 2011 by comment

[LasseJ]

It works like a charm. Thank you.

(And based on your advice I will put the prices in separate fields)

Lasse

[LasseJ]

Hmm.. it seems like there is some kind of mistake after all. Whenever "StartingDate" is the first day of a month the calculation comes out wrong (too low)

Can anybody see the mistake in the proposed script by comment?

[comment]

Yes, you are right - I got a bit confused by your method of considering {July 28 to September 27} a full month. Try the corrected formula above.

[LasseJ]

The problem when starting date is begining of the month, is solved, but it seems like its now coming out 19 $ or 38 $, short (1 day /2 days?)

[comment]

Please provide a full example.

[LasseJ]

If starting Date is February 1. and Enddate is May 14., a manual calculation comes out this way:

Basic fee: = 268

February 1 until April 30 = 3 month = 532*3 = 1,596

May 1. until May 14 = 14 single days = 19*14 = 266

Total: 2,130

When using the calculation it comes out: 2,111

I really appreciate your help. This is way out of my league

[comment]

When using the calculation it comes out: 2,111

Are you sure? I get 2130.

[LasseJ]

Hmm, strange. Here is a copy of my (well, yours!) script. Have I made a mistake when re-creating it?

Let ( [

end = Produkter::End Date + 1 ;

m = 12 * ( Year ( Produkter::End Date ) - Year ( Produkter::Arrival Date ) ) + Month ( Produkter::End Date) - Month ( Produkter::Arrival Date) - ( Day ( Produkter::End Date < Day ( Produkter::End Date)) ;

d = Produkter::End Date - Date ( Month ( Produkter::Arrival Date ) + m ; Day ( Produkter::Arrival Date ) ; Year ( Produkter::Arrival Date ) )

] ;

268 + m * 532 + d * 19

)

[comment]

Hmm, strange. Here is a copy of my (well, yours!) script. Have I made a mistake when re-creating it?

Let ( [

end = Produkter::End Date + 1 ;

m = 12 * ( Year ( Produkter::End Date ) - Year ( Produkter::Arrival Date ) ) + Month ( Produkter::End Date) - Month ( Produkter::Arrival Date) - ( Day ( Produkter::End Date < Day ( Produkter::End Date)) ;

d = Produkter::End Date - Date ( Month ( Produkter::Arrival Date ) + m ; Day ( Produkter::Arrival Date ) ; Year ( Produkter::Arrival Date ) )

] ;

268 + m * 532 + d * 19

)

Yes. BTW, it's a calculation formula, not a script.

[LasseJ]

Right on!

Thank you very much. And of course you are right, its a calculation formula, but in my case being a part of a script

Best regards