Changing a double type cell in "HH:MM:SS" format.

I have a time column which has a double type, "4.099215427200000e+09" as an example of one cell of the whole column.

Is there a way how to change the whole column format in a date format " HH:MM:SS"?

[Ameer Hamza]

What is the mapping between 4.099215427200000e+09 and HH:MM:SS?

[Ramo Rafsel]

There is no mapping between them. I had at first long numbers in the column, which I converted using an equation that I had to the numbers that I wrote the example of, but I do not know how to change its format to the time format ( In excel I just choose the option of converting the cells to HH:MM:SS but in matlab I am not sure how to do that)

[Stephen23]

"There is no mapping between them."

There must be a mapping, otherwise your question is totally meaningless. What is the epoch? What is the unit of time?

[Ramo Rafsel]

Okay I guess I didnt get the mapping question since I am not quite sure what that is.

If you get to see both screenshots that I uploaded, you ll see the last column on the first picture in Excel that has time formats "13:10:43" which I got with the same procedure that I used in matlab, but in matlab I get wrong times and I am not sure what the reason is.

I used this code line to convert the column to "HH:MM:SS" format.

exceld.calctime  = datestr(exceld.time ,'HH:MM:SS');

[Bjorn Gustavsson]

As I wrote below, matlab uses (used?) time in fractional days since start of year 0. If you feed datestr time from some other starting-point (1970 01 01 00:00:00?) in some other units (seconds for example) datestr will not know about your choise of epoch and unit. You will have to make a conversion from your/excel's time-convention to the time-convention matlab uses.

[Stephen23]

'If you get to see both screenshots that I uploaded, you ll see the last column on the first picture in Excel that has time formats "13:10:43"...'

We cannot tell from a screenshot what the actual data in that cell is. What is displayed does not tell us much, because displaying data is usually a lossy process which does not represent all of the stored value in memory.

'...which I got with the same procedure that I used in matlab'

Given that you have not described in any way the "procedure" that you attempted, and do not seem to have taken into account the different epochs that these two applications use, I am not surprised that you get very different outputs.

If you want help with this then please upload some sample data (e.g. in an Excel file) and also explain what this mystery "procedure" is. And if at all possible, please tell us the epoch and unit of the timestamp number.

Note: Excel uses fractional days since 1900 or 1904, which means that if we convert todays date into an Excel datenumber we get 44110 (this is what is stored in the cell, how it is formatted is irrelevant). Assuming a standard Excel date, your value of 4099215427.2 would be several thousand years in the future, which seems a bit unlikely... Interpreting it as posix time gives the date 24th of November 2099 at 14:57:07, which seems a bit more likely... but who knows, this is all guessing.

Not telling us the epoch and unit make it very difficult for us to help you.

[Ramo Rafsel]

The only thing that I meant by "procedure", is by calculating the time using the equation "Time cell/1000*(1/24/60/60)", to get time values and convert the format in excel manually, you ll see the used equation if you click on the first cell in the column F in the excel file. So there is no "mystery" procedure. I just used what I was given before. And pardon me I am still new to the matlab world, so I am still learning from such problems and from my mistakes also :)

See the attached excel file, and thanks a lot for the help.

[Bjorn Gustavsson]

OK, so now you've explained that the time-unit is ms (perhaps?). That takes us to the result that your first data (4.099215427200000e+09) is 47.445 days from your date-zero. If you can tell datestr what that date-zero is you've solved your problem.

Answer 1

[Steven Lord]

What duration do you expect 4.099215427200000e+09 to represent? If you give us one piece of a Rosetta stone we may be able to help interpret your data. Based on your equation this might be what you're looking for:

du = seconds(4.099215427200000e+09/1000);
du.Format = 'hh:mm:ss'

How did you obtain the value of about 4.099e9 from your Excel file? How did you read that data into MATLAB?

Looking at your file and manually copying the value from cell A1 into MATLAB, I think this is what you want:

du2 = seconds(47445960)/1000;
du2.Format = 'hh:mm:ss'

[Ramo Rafsel]

I expect the number to represent the duration of 13:10:45.

I just uploaded the list in matlab and sorted it by variable names.

I obtained the value through the calculation that I was given, which was: the given time values (Column A in picture 1)*1000/24/60/60)

The actual problem is that I want to get the same duration results I got on excel (1st Picture, column F and G. The columns G and F have the same values but G has duration format [h]:mm:ss. In Matlab I get different results.

Thanks a lot for the help!

[Stephen23]

"I obtained the value through the calculation that I was given, which was: the given time values (Column A in picture 1)*1000/24/60/60)"

I very much doubt that, you won't get anything close to 4e9:

>> val = 47444524; % cell A1 of your Excel worksheet.
>> val*1000/24/60/60 % your own calculation.
ans =
   5.4913e+05

Whatever you did to get 4e9 is something quite different, which so far you have not shown us or explained. Not that it really matters, because that very strange value does not help us anyway.

Apparently the input value is simply a duration in milliseconds (it would have saved everyone a lot of time if you had just explained that in the beginning), in which case Steven Lord's answer gives what you are looking for:

>> du = seconds(val)/1000;
>> du.Format = 'hh:mm:ss'
du = 
   13:10:44
>> du.Format = 'hh:mm:ss.SSS'
du = 
   13:10:44.524
>> round(du) % Excel rounds to the nearest second, which is an abomination.
ans = 
   13:10:45

[Ramo Rafsel]

Thank you @Stephen Cobeldick and @Steven Lord. You solved the problem. I am sorry I could't explain it the best or the easiest way. I guess I have to learn how to better articulate myself.

I made a mistake in my previous comment, it should be "(Column A in picture 1)/1000/24/60/60)" instead of "Column A*1000/24/60/60". So I got the wrong value. Thanks for pointing it out.

And yes the input value is the duration in milliseconds. So it is all clear now.

Thanks a lot again :)

Answer 2

[Bjorn Gustavsson]

Well you get an output from datestr:

datestr(4.099215427200000e+09,'yyyy mm dd HH:MM:SS')
ans =
    '3270 08 21 04:47:59'

You will have to give some additional information about the start of your time-scale, see the help and documentation of datestr, datenum ans datevec. Matlab's date-format is in fractional days since 0-0-0 0:0:0, unix-time is in seconds since 1970 01 01 00:00:00 (IIRC?).

HTH