how to pass a datetime string from strftime() as an excel sheet name using openpyxl?

Question

The purpose of the code below is to check whether or not a sheet already exists in excel workbook. If sheet exists, then set sheet as active sheet with given name. If sheet doesn't exist, then create a new sheet with given name. When I run this code within my main(), it raises a ValueError: Invalid character / found in sheet title. The statement ws = wb[job.get_startOfWeek().strftime('%M-%D-%Y')] is the format Iv'e seen from many sources to use to make a sheet active while ws = wb.create_sheet(job.get_startOfWeek().strftime('%M-%D-%Y')) is how to create a new sheet within current workbook. Both statements require that the sheet name be passed as a string which I made sure of by using .strftime('%M-%D-%Y') since the method job.get_startOfWeek() returns a datetime object. I have verified that job.get_startOfWeek() returns a datetime object so .strftime('%M-%D-%Y') is needed. I have also verified that the string returned doesn't contain a /. Is this legal syntax when using openpyxl? I couldn't find much online using openpyxl in this way.

This is the code in question

 if job.get_startOfWeek().strftime('%M-%D-%Y') in wb.sheetnames:
    ws = wb[job.get_startOfWeek().strftime('%M-%D-%Y')]
 else:
    ws = wb.create_sheet(job.get_startOfWeek().strftime('%M-%D-%Y'))

The above code is used as follows in complete main()

from openpyxl import load_workbook
import Invoice


def main():
    totalAmount = 0.0
    wb = load_workbook(filename='C:\\Users\\davis\\Desktop\\test.xlsx', read_only=False)
    ws = wb.active
    while True:

        job = Invoice.invoice()

        print("Welcome to the Invoice Generator...\nPlease follow the on screen instructions to "
              "generate your invoice \n")

        job.set_date(input("Enter the date of job in MM/DD/YYYY format: "))
        job.set_client(input("Enter client: "))
        job.set_description(input("Enter the job description: "))
        job.set_clientBilledHrs(input("Enter the client billed hrs as an integer or decimal: "))
        job.set_employerBilledHrs(input("Enter employer billed hrs as an integer or a decimal: "))
        job.set_theoreticalJobHrs(input("Enter the theoretical job hrs as an integer or a decimal: "))
        job.set_actualJobHrs(input("Enter actual number of hrs on job in [(0-23):(00-59)] format: "))
        job.set_hrsAtSite()
        job.set_hrsOffSite()
        job.set_job_amount()
        totalAmount += job.get_job_amount()

        jobInfo = [job.get_job_amount(), job.get_date(), job.get_client(), job.get_description(),
                   job.get_clientBilledHrs(), job.get_employerBilledHrs(), job.get_theoreticalJobHrs(),
                   job.get_actualJobHrs(), job.get_hrsAtSite(), job.get_hrsOffSite()]

#********************************************************************************************
This is the code in question from above

        if job.get_startOfWeek().strftime('%M-%D-%Y') in wb.sheetnames:
            ws = wb[job.get_startOfWeek().strftime('%M-%D-%Y')]
        else:
            ws = wb.create_sheet(job.get_startOfWeek().strftime('%M-%D-%Y'))

#********************************************************************************************

        if job.get_startOfWeek() <= job.get_date() <= job.get_endOfWeek():

            col = 0
        for x in jobInfo:
            ws.cell(ws.max_row + 1, col + 1, jobInfo[x])
            col += 1


        if Invoice.lastJob():
            wb.close()
            break


if __name__ == "__main__":
    main()

Answer 1

The reason you are seeing the / error is because of the date format. Please read through the format for strftime() and the options available here. I assume you are looking for the date format (in string) to be in MM-DD-YYYY. However, %M is the unit for minutes. The %D is not something strftime is able to understand and so returns date in the format of DD/MM/YYYY. You need to change the strftime(%M-%D-%YYYY) to strftime(%m-%d-%Y).

>> datetime.datetime.now().strftime("%M-%D-%Y")  
'56-05/15/22-2022'

>> datetime.datetime.now().strftime("%m-%d-%Y")  
'05-15-2022'