8 Replies Latest reply: Mar 7, 2012 8:12 AM by Celambarasan Adhimulam

# Convert date from CYYMMDD and get diff from today

Hi Everyone , I thought this should be a simple task - even for a newbie , but I'm going round in circles ! I simply need to convert a date from CYYMMDD and get the number of days between now and a due date. I can reformat the date , but then I can't use the new date to get the number of days difference. Date format is currently set as DD/MM/YYYY. Can someone give me a clue please ! Thanks , Simon

• ###### Convert date from CYYMMDD and get diff from today

Hi,

Try with this using interval function.

Interval(Today()-Date#(Right(DateField,6),'YYMMDD'),'D')

Hope it helps

Celambarasan

• ###### Convert date from CYYMMDD and get diff from today

Hi ,

Excellent - however , a couple of questions. Can this be used in a similar way to networkdays to omit weekends ? This gives negative number of days - can the fields be reversed ?

Many thanks, Simon

• ###### Convert date from CYYMMDD and get diff from today

Hi,

Ya you can use in it

Like

NetworkDays(Date#(Right(DateField,6),'YYMMDD'),Today())

Celambarasan

• ###### Convert date from CYYMMDD and get diff from today

Hi , have applied the following

NetworkDays(Date#(Right(DUEDT,6),'YYMMDD'),Today()) as Diff,

but it only gives a value for past due dates - not future ones

Regards,

Simon

• ###### Convert date from CYYMMDD and get diff from today

Hi,

For this case use condition

If(Date#(Right(DUEDT,6)<=Today(),NetworkDays(Date#(Right(DUEDT,6),'YYMMDD'),Today()),

-NetworkDays(Today(),Date#(Right(DUEDT,6),'YYMMDD'))) as Diff

Hope it helps

Celambarasan

• ###### Convert date from CYYMMDD and get diff from today

Hi , sorry - can't get that one to work , syntax error with missing closing bracket , can't get one added to work. Don't waste too much time on it - can probably work with the negative values.

Regards,

Simon

• ###### Convert date from CYYMMDD and get diff from today

Hi,

Check now

=If(Date#(Right(DUEDT,6)<=Today()),NetworkDays(Date#(Right(DUEDT,6),'YYMMDD'),Today()),

-NetworkDays(Today(),Date#(Right(DUEDT,6),'YYMMDD')))

Celambarasan

• ###### Convert date from CYYMMDD and get diff from today

Hi , this one just gives zero's for Diff - have now cheated and multiplied the Interval version by -1 to change the sign.

Thanks so much for you help - really appreciate it.

Regards,

Simon