Automatic entry of times based on conditions

Brass Contributor

Hello everyone.

For weeks now, I've been going crazy looking for a way to solve the problem I'm about to present to you.

 

I have attached the sheet I prepared.

 

In the "HOUR" column there is a way to have the times automatically based on the criteria that I want to give (for example: every Sunday I must enter the times 8:00; 10:30; 12:00; 18:30 )?

 

I thought of inserting the timetable conditions by creating counters like the ones on the right of the sheet; however, of course, if there were better methods I would gladly accept them.

 

For example, as in the sheet, all Sundays must have:

- during solar time, times 8:00 am; 10:30 am; 12:00 pm; 6:30 pm

- during summer time, the times are 8:00 am; 10:30 am; 12:00 pm; 7:00 pm

- during July and August, the times are 8:00 am and 7:30 pm

So that means, for example, this year, January 8, falling on a Sunday, must have four rows (as in the sheet). But next year, 2024, January 8 will fall on a Monday, so it will only have to have one line.

 

I hope I was clear in explaining my problem and I hope someone can help me find a solution (without using Macros and VBA).

Thank you,

Luciano

14 Replies

@Luxio1997 

Yes, it can be done with just formulas. Limitations and assumptions:

  • The formulas for column C rely on the fact that the last time for each Sunday schedule is after 18:00, and the other times are all before 18:00.
  • Google reports that Summer Time in Italy will run from 02:00 26-Mar through 03:00 29-Oct. I placed those start and end dates in cells M1 and M2, but you could put them elsewhere, with appropriate changes to the column D formulas.
  • You must manually enter the starting date into C4. If this date is a Sunday, you must manually enter the first time from the appropriate Sunday schedule into D4; otherwise, D4 should be blank.
  • The formulas work for (basically) one calendar year only. This is because I have only one date range for determining Summer Time. However, you can remove this limitation if you move them to a separate worksheet, make a lookup table based on the year of the date in column C, and change the column D formulas appropriately. (Yes, I did extend the formulas out to row 525, and those calculations are valid, as the generated dates do not reach the 2024 start of Summer Time.)

Within the column D formula:

  • Most of the complexity is in determining which of the three Sunday schedules to use. The appropriate range of cells is assigned to intermediate variable SundayRng.
  • DayCt is the number of times the date in column C for this row has appeared (so far). DayCt is used as an index into the appropriate Sunday schedule.
--for C5 (the first calculated date):
=IF( D4="", C4+1, IF(D4>TIME(18,0,0), C4+1, C4) )

--for D5 (the first calculated time-of-day):
=IF( WEEKDAY(C5,1)<>1, "", LET(MonthNum, MONTH(C5), DayCt, COUNTIF(C$3:C5,C5), SundayRng, IF(OR(MonthNum=7,MonthNum=8),N$4:N$5,IF(AND(C5>=M$1,C5<M$2),M$4:M$7,L$4:L$7)), LookupTime, INDEX(SundayRng,DayCt), LookupTime) )

 

Hi SnowMan55, beautiful solution but I have a problem: I checked and this formula doesn't exist in the Office 2013 package. How can we fix it?

@Luxio1997 

In D5:

 

=IF( WEEKDAY(C5,1)<>1, "", INDEX(IF(OR(MONTH(C5)={7,8}),N$4:N$5,IF(AND(C5>=M$1,C5<M$2),M$4:M$7,L$4:L$7)),COUNTIF(C$3:C5,C5)))

 

Fill down. See attached version.

Hi Hans, great answer.
Now there is another problem: in column C (the date column) there is the formula (for example in C5):
=IF( D4="", C4+1, IF(D4>TIME(18,0,0), C4+1, C4) )
This creates the problem that D4 will never be empty because every day there will always be times.
What can be the solution?

@Luxio1997 

The formula in column D returns "" for Monday to Saturday, so the test D4="" is valid:

S2268.png

@Hans Vogelaar 

Screenshot (18).png

 

Now that there are no other times it is valid; but when I enter the times on the other dates (for example Monday 2 January), since it is no longer empty, it will return me twice on Monday.
What can be done to avoid this problem?

@Luxio1997 

I thought that the idea was to use only formulas. If you're going to overwrite some of the formulas in column D with times, it won't work anymore, and I don't know how to repair that.

@Luxio1997 

Just as you did not initially post that you were using a ten-year-old version of Excel, you did not initially post that you would be overtyping values that the formulas generate.

 

<< when I enter the times on the other dates (for example Monday 2 January), since it is no longer empty, it will return me twice on Monday. >>
You have several options for handling this, including:

  • Rather than typing a time value, you can enter a text value that shows a time; in other words, enter an apostrophe in front of the time value as you type (e.g., typing '11:00). But this has the disadvantage of making your time data less useful for analysis and possibly more difficult to sort.
  • Although this is not the best solution, as I describe below, you can replace the column C formula with this formula (for C5; copy it down as needed):
=IF( AND(WEEKDAY(C4,1)=1, D4<>"", D4<TIME(18,0,0)), C4, C4+1 )
  • When you overtype a formula in column D with a time, also overtype the formula in column C in the next row with the next date (or the date it was before the change). Although that is not much effort, it could be tedious.
  • If you are going to be overtyping formulas with values more than a few times, as you apparently will, then I recommend that you use the formulas only at the start of a year, when you are building a new worksheet. Specifically, maintenance would be:

 

  1. Keep one "template" worksheet that contains the formulas and generated dates and times but is never filled in with other times or data.
  2. Annually, or as needed, make a copy of that "template" worksheet. Within the new worksheet, change the year (B1), initial date and time (C4 and D4), and range of Summer Time dates (M1 and M2);
  3. As needed, add/change/remove times from the three Sunday schedules. If you add times, you will need to expand the corresponding range references in all the column D formulas. If more than one scheduled time occurs after 18:00, or if a scheduled time after 18:00 is removed, you will need a new formula for column C; I cannot forecast what that formula would be.
  4. Add or delete rows at the bottom, and check that everything looks right. Save your work.
  5. Select all of column C and D, and Copy them. Without changing the selection, Paste Values into those selected cells, which replaces those formulas with their calculated date and time values. Press Escape to cancel the copy-paste activity. If the content still looks right (as it should), save your work.

There are multiple advantages to not keeping the formulas in your worksheets used for logging: the workbook is smaller, and you are not relying on fragile formulas. Now you can add special occurrences, or remove events or change calculated times if there are special circumstances, without damaging values in other rows. And this problem is avoided: Using formulas only, if any of your Sunday schedules were to change mid-year, changing a time in those schedules would change times for events that had already occurred!

 

Hi @SnowMan55 and @Hans Vogelaar ,

thank you for all the effort you are putting into this problem of mine.

Practically the formula in D4:

 

=IF(WEEKDAY(C15;2)<>7;"";INDEX(IF(OR(MONTH(C15)={7\8});N$4:N$5;IF(AND(C15>=M$1;C15<M$2);M$4:M$7;L$4:L$7));COUNTIF(C$3:C15;C15)))



is perfect but instead of the "" (and therefore the result equal to empty text) put a function (I imagine similar to the same) that allows me to put the "fer" times (ie every day except Sundays).

 

Screenshot (19).png

@Luxio1997 

That won't work.

I fixed it!
Now how do I view only the dates up to December 31st?
Because in this way it also inserts dates after December 31st, up to the cell where the formula was dragged.

@Luxio1997 

In C5:

=IF(C4="", "", IF(D4="", IF(YEAR(C4+1)<>$B$1, "", C4+1), IF(D4>TIME(18,0,0), IF(YEAR(C4+1)<>$B$1, "", C4+1), C4)))

In D5:

=IF(C5="", "", IF( WEEKDAY(C5,1)<>1, "", INDEX(IF(OR(MONTH(C5)={7,8}),N$4:N$5,IF(AND(C5>=M$1,C5<M$2),M$4:M$7,L$4:L$7)),COUNTIF(C$3:C5,C5))))

Fill down.

@Hans Vogelaar 

Since I added other conditions, I modified the formulas.

 So now what would the formula you wrote above look like?

 

IMG-20230222-WA0007.jpg

 

IMG-20230222-WA0006.jpg

@Luxio1997 

You'll have to work that out yourself, it's getting too complicated for me.