Python Forum
Set 'Time' format cell when writing data to excel and not 'custom'
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Set 'Time' format cell when writing data to excel and not 'custom'
#1
Smile 
Hi Smile ,

I am using python v3.6 with xlsxwriter. Writing time into excel cell. The problem that I see that the format cell is custom and not Time. I tried using "%H%M%S" in format cell. Also used datetime object and time object instead of string, but in all cases cell format was custom and not Time.
Any idea how can I change it to be Time format cell instead of custom?
BTW, in date I used datetime object with "/" separator and it appeared as date. Maybe I am using wrong format. I tried to use [h]:mm:ss and also hh:mm:ss.

Does anyone has working example that set the time as an time object and not custom?

Thank you for your help ,
Limor
Reply
#2
From the docs: https://xlsxwriter.readthedocs.io/
Specifically: https://xlsxwriter.readthedocs.io/workin..._time.html
format7 = workbook.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
worksheet.write('A7', number, format7)       # Feb 28 2013 12:00 PM
Reply
#3
(Mar-29-2021, 10:30 AM)Larz60+ Wrote: From the docs: https://xlsxwriter.readthedocs.io/
Specifically: https://xlsxwriter.readthedocs.io/workin..._time.html
format7 = workbook.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
worksheet.write('A7', number, format7)       # Feb 28 2013 12:00 PM

Hi,

Thank you for you reply, appreciate it.
I tried it and it results with general type and not Time format cell Cry . you can look right click -> Format cell and see it is general type and not time
─░mage


Thanks,
Limor
Reply
#4
here's the example from text, verbatim:
import xlsxwriter

workbook = xlsxwriter.Workbook('date_examples.xlsx')
worksheet = workbook.add_worksheet()

# Widen column A for extra visibility.
worksheet.set_column('A:A', 30)

# A number to convert to a date.
number = 41333.5

# Write it as a number without formatting.
worksheet.write('A1', number)                # 41333.5

format2 = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write('A2', number, format2)       # 28/02/13

format3 = workbook.add_format({'num_format': 'mm/dd/yy'})
worksheet.write('A3', number, format3)       # 02/28/13

format4 = workbook.add_format({'num_format': 'd-m-yyyy'})
worksheet.write('A4', number, format4)       # 28-2-2013

format5 = workbook.add_format({'num_format': 'dd/mm/yy hh:mm'})
worksheet.write('A5', number, format5)       # 28/02/13 12:00

format6 = workbook.add_format({'num_format': 'd mmm yyyy'})
worksheet.write('A6', number, format6)       # 28 Feb 2013

format7 = workbook.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
worksheet.write('A7', number, format7)       # Feb 28 2013 12:00 PM

workbook.close()
and the results:
   
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  dataframe cell conditional format by row SriRajesh 0 347 Apr-24-2021, 11:51 AM
Last Post: SriRajesh
  How to append a value to specific excel cell using openpyxl hobbyist 0 560 Mar-05-2021, 07:14 PM
Last Post: hobbyist
  Extract data from PDF page to Excel nathan_nz 1 398 Oct-29-2020, 08:04 PM
Last Post: Larz60+
  I try to import data from Excel table to Word Template. NewbiePyPy 0 536 Oct-21-2020, 12:25 PM
Last Post: NewbiePyPy
  Writing to existing excel sheet jksvend 0 416 Oct-12-2020, 11:19 AM
Last Post: jksvend
  ValueError: time data 'None' does not match format '%Y-%m-%dT%H:%M:%S.%f' rajesh3383 4 2,539 Sep-03-2020, 08:22 PM
Last Post: buran
  Formatting Data/Time with Pyodbc and openpyxl bearcats6001 0 580 Aug-17-2020, 03:44 PM
Last Post: bearcats6001
  how to retain time format in df.to_csv Mekala 2 745 Aug-07-2020, 07:04 AM
Last Post: buran
  Find data using a period of time in SQLITE3 SmukasPlays 2 593 Jul-30-2020, 02:02 PM
Last Post: SmukasPlays
  Issue accessing data from Dictionary/List in the right format LuisSatch 2 627 Jul-25-2020, 06:12 AM
Last Post: LuisSatch

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020