0

I have a problem with a report I am reading with openpyxl. Sometimes cell formats in a file I receive are not correct, and instead of "10.03.2020" (cell format Date) I get "43900,88455" (cell format General).

I tried google, openpyxl documentation and StackOverflow but that did not bring me any closer to the solution. Would you be able to help and advise how to switch cell format to Short Date, please?

Below did not work, I tried many other ideas but still in a dead end. I need correct dates for other operations within this script.

    def sanitizeDates(self):
        # pass
        for i in range(3, self.fileLength-1):
            self.mainWs.cell(i, 4).number_format = 'dd/mm/yyyy'
            self.mainWs.cell(i, 16).number_format = 'dd/mm/yyyy'

Copy comment: So I have tried 

print("Cell (4) type is: " + str(type(self.mainWs.cell(i, 4).value)) + " and current value is " + str(self.mainWs.cell(i, 4).value)) 
print("Cell (16) type is: " + str(type(self.mainWs.cell(i, 16).value)) + " and current value is " + str(self.mainWs.cell(i, 16).value))

that results in Cell (4) type is: <class 'datetime.datetime'> and current value is 2020-03-10 22:41:41
Cell (16) type is: <class 'float'> and current value is 43900.9475
Excel displays it as "General" = "43900,88455"

stovfl
  • 14,998
  • 7
  • 24
  • 51
NewBe
  • 61
  • 1
  • 7
  • @stovfl You are right, this is not correct date at all. When I click in Excel on the ribbon and switch from General to Short Date it results in a valid date. Example: `43900,88455 ` becomes `10.03.2020 21:13:45` – NewBe Mar 19 '20 at 13:44
  • Thank you for your help anyway! I am not sure, if Excel see it as string (it sits under General, so it can be float). Python recognize it as float. – NewBe Mar 19 '20 at 14:24

1 Answers1

0

So, I have figured this out after all. @stovfl thanks for your hints, this brought me to an idea what to look for and as a result, how to solve my issue.

Original thread on StackOverflow is available here: https://stackoverflow.com/a/29387450/5210159

Below is a working code:

   def sanitizeDates(self):
        for i in range(3, self.fileLength - 1):
            # Check which cells should be sanitized and proceed
            if isinstance(self.mainWs.cell(i, 4).value, float) and isinstance(self.mainWs.cell(i, 16).value, float):
                print(str(self.mainWs.cell(i, 4).value) + " / " + str(self.mainWs.cell(i, 16).value) + " -> " + str(self.convertDate(self.mainWs.cell(i, 4).value)) + " / " + self.convertDate(self.mainWs.cell(i, 16).value))
                self.mainWs.cell(i, 4).value = self.convertDate(self.mainWs.cell(i, 4).value)
                self.mainWs.cell(i, 16).value = self.convertDate(self.mainWs.cell(i, 16).value)
            elif isinstance(self.mainWs.cell(i, 4).value, float):
                print(str(self.mainWs.cell(i, 4).value) + " -> " + str(self.convertDate(self.mainWs.cell(i, 4).value)))
                self.mainWs.cell(i, 4).value = self.convertDate(self.mainWs.cell(i, 4).value)
            elif isinstance(self.mainWs.cell(i, 16).value, float):
                print(str(self.mainWs.cell(i, 16).value) + " -> " + str(self.convertDate(self.mainWs.cell(i, 16).value)))
                self.mainWs.cell(i, 16).value = self.convertDate(self.mainWs.cell(i, 16).value)


    def convertDate(self, dateToConvert):
        # Thank you, StackOverflow <3
        # https://stackoverflow.com/questions/29387137/how-to-convert-a-given-ordinal-number-from-excel-to-a-date
        epochStart = datetime(1899, 12, 31)
        if dateToConvert >= 60:
            dateToConvert -= 1  # Excel leap year bug, 1900 is not a leap year
        return epochStart + timedelta(days=dateToConvert)

After execution I get following results:

43899.89134259259 -> 2020-03-09 21:23:32

43900.9475 -> 2020-03-10 22:44:24
NewBe
  • 61
  • 1
  • 7