Forum Discussion
Automatic entry of times based on conditions
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
Hi SnowMan55 and HansVogelaar ,
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).
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.
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?
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.
