Convert this number to a date, its big

[sundance82]

Hi all

I just wonder if it was possible to convert this number to a date format, its a big number

42251701101108

<tbody>
</tbody>

so its backwards and is the following

42 25 17 01 10 11 08
ss:mm:hh doe DD MM YY

Doe if day of the week

Can anyone help me convert ths to a date format, I am a novice with programming in excel. I tried this but didnt think it would work, I am assuming you can on have one MID in a formula.

=VALUE(LEFT(F2,2)&":"&MID(F2,3,2)&":"&MID(F2,5,2)&""&MID(F2,7,2)&"/"&MID(F2,9,2)&"/"&MID(11,2)&"/"&RIGHT(F2,2))

Cheers

Rob

 

[hurgadion]

Hi,
try the Formula

=DATE(2000+RIGHT(A1,2),MID(A1,11,2),MID(A1,9,2))+TIME(MID(A1,5,2),MID(A1,3,2),LEFT(A1,2))

and choose the proper Format, best regards.

 

[sundance82]

Thats excellent, that worked thanks, just another quick question can you calculate the number of days between 2 date values with the time in them e.g.

11/11/2008 17:25:42 and 10/11/2008 14:25:00

Kind regards

Rob

 

[Robert Mika]

If your dates are in A1 and B1
=B1-A1

then use Custom Format [h]:mm to get the hours difference.

 

[hurgadion]

This WorkSheet Function may be useful for You:

=DATEDIF(A1,A2,"D")

where in A1 is Start Date and in A2 is End Date, best regards.

 

[sundance82]

Thanks very much for your help again, unfortunately this does not seem to work when you have the datetime in there. trying both methods above gives me #Value!. My dates are both in custom format dd/mm/yyyy hh:mm:ss

Kind regards

Rob

 

[hurgadion]

You have to change the Format of Data to a Date Format similar to the above Formula with Functions DATA And TIME, this is Your HomeWork, best regards.

 

[sundance82]

Cheers, sorry was that meant to be DATA or DATE?

 

[hurgadion]

I'm sorry: DATA should be DATE of course... Best regards.

 

[sundance82]

Hi would I be right to do the following to get the number of days

11/11/2008 08:56:02 - 10/11/2008 08:58:49



=DATE(LEFT(N2,2),MID(N2,4,2),MID(N2,7,4))+TIME(MID(N2,12,2),MID(N2,15,2),MID(N2,18,2)) - DATE(LEFT(M2,2),MID(M2,4,2),MID(M2,7,4))+TIME(MID(M2,12,2),MID(M2,15,2),MID(M2,18,2))


Kind regards

Rob