Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4787 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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 $

Posted (edited)

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 by comment
Posted

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?

Posted

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?)

Posted

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

Posted

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

)

Posted

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.

Posted

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

This topic is 4787 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.