Convert date from CYYMMDD and get diff from today

[#1]

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

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this using interval function.

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

Hope it helps

Celambarasan

[#3]

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

[CELAMBARASAN]

Hi,

     Ya you can use in it

     Like

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

Celambarasan

[#5]

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

[CELAMBARASAN]

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

[#7]

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

[CELAMBARASAN]

Hi,

     Check now

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

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

Celambarasan

[#9]

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