Feb 15 2023 07:04 AM
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
Feb 17 2023 11:23 PM
Yes, it can be done with just formulas. Limitations and assumptions:
Within the column D formula:
--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) )
Feb 18 2023 05:00 AM
Feb 18 2023 05:11 AM
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.
Feb 18 2023 05:32 AM
Feb 18 2023 06:05 AM
Feb 18 2023 06:13 AM
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?
Feb 18 2023 07:35 AM
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.
Feb 18 2023 08:35 AM
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:
=IF( AND(WEEKDAY(C4,1)=1, D4<>"", D4<TIME(18,0,0)), C4, C4+1 )
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!
Feb 20 2023 05:39 AM - edited Feb 20 2023 05:42 AM
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).
Feb 22 2023 05:12 AM
Feb 22 2023 05:24 AM
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.
Feb 22 2023 06:17 AM
Since I added other conditions, I modified the formulas.
So now what would the formula you wrote above look like?
Feb 22 2023 06:22 AM
You'll have to work that out yourself, it's getting too complicated for me.