# Convert this number to a date, its big

#### sundance82

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi,
try the Formula
Code:
``=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.

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

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

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

This WorkSheet Function may be useful for You:
Code:
``=DATEDIF(A1,A2,"D")``
where in A1 is Start Date and in A2 is End Date, best regards.

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

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.

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

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

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

Replies
3
Views
640
Replies
1
Views
215
Replies
17
Views
7K
Replies
12
Views
724
Replies
17
Views
5K

1,203,663
Messages
6,056,623
Members
444,878
Latest member
SoupLaura

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back