Search code examples
pythonexcelpandasxlwings

xlwings cell value wrong

in excel I have a cell with the value 1:23.456 with the the format m:ss.000 (minute, seconds and milliseconds)

now I want to read the value with python xlwings and pandas:

import xlwings as xw
import pandas as pd

excelData = xw.Book(path)
data = pd.DataFrame(excelData.sheets[sheetName].used_range.value)
print(data[20][7])

but instead of 1:23.456 I get 0.000965925925925926 as the output.

How do I get the right number as the output? (1:23.456)

Solution

  • I don't know of a simple, built-in way to do this. However, you can change the format once the data is in the DataFrame (I assume there will be a column of data that needs to be changed:

    val = data[20][7] # Change this to the range of data you need to be altered.

(dt.datetime.min + dt.timedelta(val)).time().strftime("%M:%S.%f")

    The number you are seeing is the ratio of the time to a full day.

    The code above starts at the minimum time dt.datetime.min and adds on the time of your value dt.timedelta(val), then converts this to the format you want (but with 6 decimals). If you specifically want 3, you could follow the steps of this answer