<div>
The integer of the date value is the number of days since 1900. So the value 1 represents 1/1/1900. The decimal portion is the time in a fraction of a day. For example, if the decimal portion was .5, the time would be 12:00pm (noon)<br />
<br />
So to get the amount of time in between two dates, just subtract one from the other. If you are trying to convert the decimal into fractions of an hour, multiply it by 24. There are also Excel functions like hours() and minutes() that might help you.
</div>


<div>
So if I understand right, I need to do 4287037037-42403 which equals 4286994634, which excel equates to #####, and if I convert on Epoch Converter I get Fri, 06 Nov 2105 23:50:34 GMT which isnt correct :-(
</div>


<div>
just put your datetime values inside the text function like this.<br />
<br />
<br />
=TEXT(42403.4287037037,&quot;dd<wbr />d dd/mmm/yyyy h:mm AM/PM;@&quot;)<br />
<br />
text function has two arguments the value itself. either you can hard code it or it can be a cell reference, then second argument of its syntax is the format and you put that inside double quotes.<br />
<br />
see the example, i have used.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/03_w10/1084184/Book2.xlsb" target="_blank" class="file-inline" title="Book2.xlsb (12 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Book2.xlsb</a>
</div>


Book2.xlsb

<div>
If you need to keep the values in such a format that they can be used for calculations, assuming your '42403.4287037037' is in cell A1:<br />
<br />
Formulas in separate cells:<br />
=INT(A1) formatted with suitable date format (mm/dd/yyy or dd/mm/yy depending on location) will show the date.<br />
<br />
=MOD(A1,1) formatted as hh:mm:ss will give the time portion.<br />
<br />
Thanks<br />
Rob H
</div>


<div>
Sorry, I need to convert back into this number as well, so need to understand how it is constructed.<br />
<br />
I know the integer is the mm/dd/yyyy and after the decimal place is hh:mm:ss, but when I convert it using epcoh converter it doesnt match
</div>


<div>
You can format the cell as datetime
</div>


<div>
If epich is giving incorrect results then don't use it.<br />
<br />
Don't understand what you mean by &quot;convert back to this number&quot;.
</div>


<div>
Excel actually uses the number behind the date at all times. &nbsp;It only changes the **display format** of this to show you what you want to see, be it dd/mm/yy or hh:mm:ss ddd mmm yyyy.<br />
<br />
Epoch time is Unix-based, and starts from 1/1/1970, so is wildly different. &nbsp;I don't see how it will help you here. &nbsp;<br />
<br />
if you only want the DATE, not time, just apply a date-only format to the cells.<br />
Or use the INT(cell) function to strip out the decimal part (thus losing the time).
</div>


<div>
note, the whole numbers represent DAYS, and the decimal represents HOURS. &nbsp;Your 2nd comment above has this round the wrong way.
</div>


<div>
As with most stuff on EE, it's easier if you post a sample sheet. &nbsp;This should have a moderate range of your typical data, but of course not contain anything that your employer would not want shared. &nbsp;<br />
<br />
Even if you just mock-up your desired output, we can go from there. &nbsp;<br />
<br />
for more info on Excel and time, see this:<br />
<a href="http://www.cpearson.com/excel/datetime.htm" rel="ugc">http://www.cpearson.com/excel/datetime.htm</a>
</div>


<div>
To refer back to your earlier column, Excel is display the result as #### because either the column isn't wide enough or the resulting number isn't translatable to a date. In this case it's the latter. I tried it myself and got the same result. 4286994634 days since 1/1/1900 is not a date Excel understands.<br />
<br />
Your second number, 42403 makes more sense. That is 2/3/2016 as you said.<br />
<br />
Don't forget, the date format is just a way of displaying the underlying date serial number.
</div>


<div>
Tonelm54, is there any chance you could provide some insight as to why the answer you accepted solved your problem. You didn't mention that you needed a VBA solution at the beginning.<br />
<br />
Thanks,<br />
Brian B (TBone2K)
</div>


<div>
<div class="content wysiwyg-content">
Im trying to convert a set of values from an integer number to Excel time, but cant figure out the conversion.<br />
<br />
In Excel I have a value which is stored as '42403.4287037037', which if I use Excel to convert it then it outputs '03/02/2016 &nbsp;10:17:20' which is right, however I need to keep in in the '42403.4287037037' and also convert a date into 'whatever' to get times between times, so I cannot convert the number to date/time in a whole.<br />
<br />
I thought it was an EPOCH time, however it has a decimal place in, and if use a convertor such as <a href="http://www.epochconverter.com/" rel="ugc">http://www.epochconverter.<wbr />com/</a> the value 42403.4287037037 returns &quot;Thu, 09 Jun 1983 19:18:07&quot; which is completely different.<br />
<br />
Anyone have any idea what this value is and how to convert to/from it?
</div>
</div>


Im trying to convert a set of values from an integer number to Excel time, but cant figure out the conversion.

In Excel I have a value which is stored as '42403.4287037037', which if I use Excel to convert it then it outputs '03/02/2016  10:17:20' which is right, however I need to keep in in the '42403.4287037037' and also convert a date into 'whatever' to get times between times, so I cannot convert the number to date/time in a whole.

I thought it was an EPOCH time, however it has a decimal place in, and if use a convertor such as http://www.epochconverter.com/ (http://www.epochconverter.com/) the value 42403.4287037037 returns "Thu, 09 Jun 1983 19:18:07" which is completely different.

Anyone have any idea what this value is and how to convert to/from it?

Excel time

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.