Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two dates and I need a formula to subtract the start date from end date and get back the number of weeks and days that are between the two dates. Ex: 3/10/14 - 3/1/14 = 1 week and 3 days.
Hi Bradley,
this should work:
=floor((date#('3/10/14') - date#('3/1/14')) / 7) & ' Week(s) ' & mod((date#('3/10/14') - date#('3/1/14')) +1, 7) & ' Day(s)'
or with fields:
=floor((EndDate - StartDate) / 7) & ' Week(s) ' & mod((EndDate - StartDate) +1, 7) & ' Day(s)'
- Ralf
You need to understand the fundamentals of Dates in QV before you tackle these calcs.
Here is an age old but always valid 1 pager which is mandatory.....
Hey Bradley,
Attached with answer.
Thanks
AJ
Hi Bradley,
this should work:
=floor((date#('3/10/14') - date#('3/1/14')) / 7) & ' Week(s) ' & mod((date#('3/10/14') - date#('3/1/14')) +1, 7) & ' Day(s)'
or with fields:
=floor((EndDate - StartDate) / 7) & ' Week(s) ' & mod((EndDate - StartDate) +1, 7) & ' Day(s)'
- Ralf
Ajay has given good answer on Script side.
UI side can be done by
Where End = EndDateField and Start = StartDateField
=Floor(Interval(End-Start,'dd')/7) &' Weeks ' & Mod(Interval(End-Start+1,'dd'),7)& ' Days'
Thank you Ralf, just what I was looking for.