Skipping certain amount of cells if a condition is true in a for loop (Python)

Question

I'm trying to tell python to insert "No work" in certain amount of cells, depending on how many days has passed since the codes was run.

So basically, let imagine that the code is run on a Friday and then a few day passes before it's started again. I want python to understand the number of days that has passed and insert "No work" in x numbers of cells in a excel file.

I've been using datetime module to calculate the number of days the code has been inactive in the "excel_update" function but find it very difficult to skip corresponding number of days in the for loop.

Can someone please tell me how this is done and explain you'r reasoning?

# ***** IMPORTS *****
import tkinter as tk
from unittest import skip
from openpyxl import Workbook
from openpyxl import load_workbook
import datetime as dt


# ***** VARIABLES *****
# use a boolean variable to help control state of time (running or not running)
running = False
# time variables initially set to 0
hours, minutes, seconds, total_time = 0, 0, 0, 0

# ***** NOTES ON GLOBAL *****
# global will be used to modify variables outside functions
# another option would be to use a class and subclass Frame

# ***** FUNCTIONS *****
# start, pause, and reset functions will be called when the buttons are clicked
# start function
def start():
    global running
    if not running:
        update()
        running = True

# pause function
def pause():
    global running
    if running:
        # cancel updating of time using after_cancel()
        stopwatch_label.after_cancel(update_time)
        running = False

# reset function
def reset():
    global running
    if running:
        # cancel updating of time using after_cancel()
        stopwatch_label.after_cancel(update_time)
        running = False
    # set variables back to zero
    global hours, minutes, seconds
    hours, minutes, seconds = 0, 0, 0
    # set label back to zero
    stopwatch_label.config(text='00:00:00')
    

# update stopwatch function
def update():
    # update seconds with (addition) compound assignment operator
    global hours, minutes, seconds, total_time
    seconds += 1
    total_time += 1
    if seconds == 60:
        minutes += 1
        seconds = 0
    if minutes == 60:
        hours += 1
        minutes = 0
    # format time to include leading zeros
    hours_string = f'{hours}' if hours > 9 else f'0{hours}'
    minutes_string = f'{minutes}' if minutes > 9 else f'0{minutes}'
    seconds_string = f'{seconds}' if seconds > 9 else f'0{seconds}'
    # update timer label after 1000 ms (1 second)
    stopwatch_label.config(text=hours_string + ':' + minutes_string + ':' + seconds_string)
    # after each second (1000 milliseconds), call update function
    # use update_time variable to cancel or pause the time using after_cancel
    global update_time
    update_time = stopwatch_label.after(1000, update)


# Fill in the excel file with times   
def excel_update():
    global hours, minutes, seconds, total_time
    dest_filename = r"C:\Users\abbas\OneDrive\Desktop\Prog\Flex\tider.xlsx"
    wb = load_workbook(dest_filename)
    ws = wb.active
    
    days = [0]
    todays_date = dt.datetime.today()
    the_day = todays_date.day
    days.append(the_day)
    time_difference = the_day - days[-2]
    #time_difference = 4
    
    # Put value in next empty cell in specified region (if there is one).
    try:
        for row in ws.iter_rows(min_row=1, max_row=3, max_col=3):
            for cell in row: 
                if time_difference >=2:
                    next 
                if cell.value is None:
                    cell.value = f"{hours} h and {minutes} min"
                    raise StopIteration
    except StopIteration:
        pass

    wb.save(dest_filename)  # Update file.
  
 
# ***** WIDGETS *****
# create main window
root = tk.Tk()
#root.geometry('450x200')
root.resizable(width=False, height=False)
root.title('Stopwatch')

# label to display time
stopwatch_label = tk.Label(text='00:00:00', font=('Arial', 20))
stopwatch_label.pack()

# start, pause, reset, stop buttons
start_button = tk.Button(text='start', height=3, width=5, font=('Arial', 10), command=start)
start_button.pack(side=tk.LEFT)

stop_button = tk.Button(text='stop', height=3, width=5, font=('Arial', 10), command=lambda:[root.quit(), excel_update()])
stop_button.pack(side=tk.LEFT)

pause_button = tk.Button(text='pause', height=3, width=5, font=('Arial', 10), command=pause)
pause_button.pack(side=tk.LEFT)

reset_button = tk.Button(text='reset', height=3, width=5, font=('Arial', 10), command=reset)
reset_button.pack(side=tk.LEFT)

# ***** MAINLOOP *****
# run app
root.mainloop()

Answer 1

What I see in your code is that you are just inserting an hour/min text entry in the Excel cell when clicking the stop button (after clicking start button); f"{hours} h and {minutes} min".

So when you run the app again there is just the 'Xh and Ymin' entry in the cell. When excel_update is called it;

1. Creates a list 'date' with value 0
2. Gets todays date assigned to 'todays_date'
3. Gets the current dates day and appends that to the list 'date'
4. Calculates 'time_difference' as date[-2] which will always be the first element you assigned when creating the list, subtracted from the current date i.e. will always be the current date (The list only has two elements so [-2] is always the first element you assigned at creation)

Either way the main point is you would not be able to calculate the time difference without a record of when the last entry was made. You probably need to include the current date/time as well in another cell either as a single 'last update' value or per entry made, whichever works best.

Then when running update_excel again read the 'last update' date in order to get the date and this would be the first entry in your date list. Bear in mind that such a calculation is too simplistic however as it doesn't account for the next month(s) so you should use the full date, i.e. if you run it on April 30 then May 6 the days difference is not 6 - 30.

---

Additional details The following is some additional information that should help. There will likely be more issues but this should help work on the main requirements as stated

(1) As mentioned to record the last time an update was made it is probably easiest to just update a cell with the current date before saving the workbook. You have the variable

todays_date = dt.datetime.today()

so you can just use that. It presumably should be fine to use the same cell each time, eg 'E2' (if we are leaving row 1 for Headers).

ws['E2'].value = todays_date
wb.save(dest_filename)  # Update file.

Then when running the update_excel function one of the first lines after the definitions would be to read this last update value.

last_update = ws['E2'].value

Calculation of the days missed can then be made by subtracting this value from the todays_date variable using the 'days' attribute to get the actual number of days. The .days attribute will provide an integer value corresponding to the number of days between the two dates and will give the correct value across different months.

time_difference = (todays_date-last_update).days 

(2) The cell update code updates the 6 cells; A2, B2, C2, A3, B3, C3 in sequence based on the cell being empty. The value being either

1. 'in-active'??? if a day(s) is missed based on the time_difference calculation being 2 or greater
2. the h/m time stamp.

From what you are saying each of these 6 cells corresponds to 1 day. This means the app is only ever run one time in a day? If so there is no protection if its run a second or subsequent time in the same day, the app will write a new entry in the next empty cell and there is no way you'd know. Therefore you might want to also use the last_update value in this part of the code to determine if/how the cell should be updated. You'll have to decide how this should be handled.

1. Ignore the new h/m value
2. Overwrite the existing h/m value
3. Add the new h/m value from the stop watch to the last update h/m and overwrite the new value to the same cell.

(3) The next issue is once the 6 cells are filled, no more updates will be written since your conditional check is for an empty cell. Therefore you need to determine how you want to handle that situation;

1. Start overwriting from the starting cell A2 again. If you do that I'm not sure how you would know which cell was last updated.
2. Clear all cells the next run after cell C3 is updated so all cells are empty before writing to cell A2 again, this will allow you to always see what cell was last updated. Given the static nature of your cell range you can just check the cell C3, ws['C3'].value, first. If it has a value then clear all cells before going onto the ws.iter_rows part of the code.

(4) The 'in-active' check will also need to change. As its currently written if the time difference is greater than a day (time_difference >= 2) then all 6 cells will be overwritten as that would be true before any check for the next empty cell. This should be incorporated into the same conditional check like the example below.

In the example below; if the current cell in the iteration is empty then based on the time difference either write 'in-active' or the h/m text. If time_difference greater or equal to 2 its value is reduced by 1 each cycle after writing the 'in-active' text so after each missed day is filled in the current day h/m value is then written in the next empty cell. The loop breaks out of the iteration at that point so no more cells are updated.

    for row in ws.iter_rows(min_row=2, max_row=3, max_col=3):
        for cell in row:
            if cell.value is None:
                if time_difference >= 2:
                    cell.value = 'in-active'
                    time_difference -= 1
                else:
                    cell.value = f"{hours} h and {minutes} min"
                    break

The above code may change depending on the incorporation of other checks mentioned in other points.

Answer 2

You are looking for continue.