4 Replies Latest reply on Apr 7, 2017 11:27 PM by Ajeet ...

date difference irrespective of start and end date

Hi guys,

I have to create a calculation with the logic:

Day ( date-time-year field1) < Day ( date-time-year field2) :::::::  output would look like ::::: 31 March 2017 < 2 April 2017

Though i can use DATEDIFF function but i have to specify end and start date which i do not want. Similarly, DATETPART function gives only integral values, and DATETRUNC function gives day with time as well which is not helpful in my case as i want to compare with two days only. ANy help please

Thanks !

Ajeet

• 1. Re: date difference irrespective of start and end date

Hi Ajeet,

Not sure but find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.

1. Notation: if [field1]<[field2] then str(int([field 1 Day]))+" "+[Month Field 1]+" "+[field 1 Year]+" < "+str(int([field 2 Day]))+" "+[Month Field 2]+" "+[field 2 Year] else "" END

2. Field 1 Day: mid(str([field1]),9,2)

3. Field 2 Day: mid(str([field2]),9,2)

4. Field 1 Month: mid(str([field1]),6,2)

5. Field 2 Month: mid(str([field2]),6,2)

7. Field 1 Year: mid(str([field1]),1,4)

8. Field 2 Year: mid(str([field2]),1,4)

9. Month Field 1:

if int([field 1 Month])=1 then "January"

ELSEIF int([field 1 Month])=2 then "February"

ELSEIF int([field 1 Month])=3 then "March"

ELSEIF int([field 1 Month])=4 then "April"

ELSEIF int([field 1 Month])=5 then "May"

ELSEIF int([field 1 Month])=6 then "June"

ELSEIF int([field 1 Month])=7 then "July"

ELSEIF int([field 1 Month])=8 then "Augustus"

ELSEIF int([field 1 Month])=9 then "Septemer"

ELSEIF int([field 1 Month])=10 then "October"

ELSEIF int([field 1 Month])=11 then "November"

ELSEIF int([field 1 Month])=12 then "December"

END

10. Month Field 2:

if int([field 2 Month ])=1 then "January"

ELSEIF int([field 2 Month ])=2 then "February"

ELSEIF int([field 2 Month ])=3 then "March"

ELSEIF int([field 2 Month ])=4 then "April"

ELSEIF int([field 2 Month ])=5 then "May"

ELSEIF int([field 2 Month ])=6 then "June"

ELSEIF int([field 2 Month ])=7 then "July"

ELSEIF int([field 2 Month ])=8 then "Augustus"

ELSEIF int([field 2 Month ])=9 then "Septemer"

ELSEIF int([field 2 Month ])=10 then "October"

ELSEIF int([field 2 Month ])=11 then "November"

ELSEIF int([field 2 Month ])=12 then "December"

END

Regards,

Norbert

• 2. Re: date difference irrespective of start and end date

Hi Ajeet - can you elaborate a bit more? It's difficult to say exactly what you're trying to accomplish. Perhaps a spreadsheet/screenshot/workbook would help?

• 3. Re: date difference irrespective of start and end date

Hi Ajeet,

Not sure this is what you are excepting.

i am adding sample calculation and result as well.

if [Start Date]>[end date]

THEN

str(DAY([Start Date]))+" "+DATENAME('month',[Start Date])+" "+STR(YEAR([Start Date]))+">"+

str(DAY([end date]))+" "+DATENAME('month',[end date])+" "+STR(YEAR([end date]))

END

seeta

• 4. Re: date difference irrespective of start and end date

Norbert Maijoor

Ben Neville

seeta t

Sorry for the inconvenience caused: Here is the exact problem:

I have two fields:

- date-time1 field with inputs as 1 April 2017 01:00:00 PM

-date-time2 field with inputs as 2 April 2017 09:00:00 PM

The intent is to achieve the following output:

if    Day(date-time1)    >    Day(date-time2)  then 1 else 0 end

Thus, my calculation only takes input Day-mmm-yyyy

Thanks !