• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Place number 1 conicide cells with times

deciog

Active Member
Gentlemen, good morning.

Only formulas for Excel 2016

I need to put number 1 in the cells that coincides with the time intervals in the initial and final period, according to the attached model.

Thank you very much in advance

Decio
 

Attachments

  • Modelo 1.xlsx
    11.9 KB · Views: 9
deciog
Two questions:
1) if 'End Time' is 08:00 and 'Total Hours Rounds' is '03:00' ... Your sample results has ... four times '1' ... ?
2) if 'End Time' is '21:00' then the last '1' is ... '21:00' ... and if 'End Time' is '23:00' then the last '1' is ... '21:00' ... ?
 
vletm, thank you

Yes, if the schedule ends at 08:00 and starts at 05:00
we have 05:00, 06:00, 07:00 and 08:00 are 4 markings

Decio
 
deciog
two questions - one answer ...
1) if 'Total Hours Rounds' is '03:00' ... why four '1's?
skipped
2) if any logic then those the last '1's should be in different cell ... or ... what would be a logic? 21:00 and 23:00 are different times or .. ?
 
vletm.

Second question, the translation of Ingues into Portuguese Brazil, I was in doubt, so I didn't answer

I need to make an appointment from the beginning to the end, I am using number 1, it can be letter X, the important thing is to mark the hours of the interval from beginning to end both inclusive

Ex:. If the time I finish is 21:00 then mark last 21:00, if the time is 23:00 then the last 23:00, but if the last day is 04:00 and the first is 20:00, then you have to mark 00:00, 01:00, 02:00, 03:00, 04:00 skip the others and mark 20:00, 21:00, 22:00, 23:00

I hope I answered

Thanks

I use Google translator, I don't know if it's translating correctly, sorry
Decio
 
deciog
Still - with or without Google translation
... something no match for me - sorry - check snapshot from Your file.
I added some colors and some texts outside of Your data.
Screenshot 2020-01-18 at 18.32.50.png
With other logic ... eg there should be 25x'1' with row three times.
 
I encountered two areas of difficulty with your problem. The main one is to decide the best formula when the timespan crosses midnight. To get hours from the next day to show on the current day plan I used the formula
= IF( overnight?, 1 - (Time>end)*(Time<start), (Time>=start)*(Time<=end) )
in cell F3 and it spills to fill the table (CSE, implicit intersection or relative referencing can all be made to work).
To identify overnight timespans I checked whether the datetimes were the same day or not. Thus
=INT(startTime)<INT(EndTime)
indicates overnight.

The other problem I encountered was that the equality conditions between dates tended to fail because of rounding error. To get over this problem, I had to define the start and end arrays to be the time of day in minutes (allowing the comparison to be between integers) so start and end were
= MOD( ROUND(1440*startTime,0), 1440 )
= MOD( ROUND(1440*EndTime,0), 1440)

while time was
= ROUND(1440*timeHour,0)
 

Attachments

  • Modelo 1 (PB).xlsx
    17.9 KB · Views: 6
vletm, good morning.

Total hours are correct, but the markings must include the beginning and ending time, thank you.

Peter, good morning.

I like the way your formula programming works, it works perfectly thank you very much

John Jairo V, Good morning.

Also love your formula works perfectly thank you very much

To you a big hug and Blessings!

Decio
 
deciog
Did You watch that snapshot?
Rows 16 and 19 - Your '1' - both rows seems to end at 21:00. ( and 19 rows 'End Time' is ... 23:00 ).
>> As You wrote: according to the attached model. <<
As those all works for You, then only that matters.
 
Hi all,

Just for reference, I've made a rather straightforward solution with Power Query. One "ugly" step is to handle the flag of the last hour as explained in #5.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1),
    SetAsDateTime = Table.TransformColumnTypes(AddIndex,{{"Start Time", type datetime}, {"End Time", type datetime}}),
    SubtractTime = Table.AddColumn(SetAsDateTime, "Subtraction", each [End Time] - [Start Time], type duration),
    CalculateDurationTotalHours = Table.TransformColumns(SubtractTime,{{"Subtraction", Duration.TotalHours, type number}}),
    RenameAsTotalHours = Table.RenameColumns(CalculateDurationTotalHours,{{"Subtraction", "Total Hours"}}),
    GetListOfDateTimes = Table.AddColumn(RenameAsTotalHours, "Custom", each List.DateTimes([Start Time],if [Total Hours]<24 then [Total Hours]+1 else [Total Hours],#duration(0, 1, 0, 0))),
    ExtractListToRows = Table.ExpandListColumn(GetListOfDateTimes, "Custom"),
    ExtracteOnlyTime = Table.TransformColumns(ExtractListToRows,{{"Custom", DateTime.Time, type time}}),
    SortAscending = Table.Sort(ExtracteOnlyTime,{{"Custom", Order.Ascending}}),
    AddBoolean1 = Table.AddColumn(SortAscending, "Custom.1", each 1),
    PivotTimes = Table.Pivot(Table.TransformColumnTypes(AddBoolean1, {{"Custom", type text}}, "nl-BE"), List.Distinct(Table.TransformColumnTypes(AddBoolean1, {{"Custom", type text}}, "nl-BE")[Custom]), "Custom", "Custom.1"),
    SortOnIndex = Table.Sort(PivotTimes,{{"Index", Order.Ascending}}),
    RemoveRedundantColumns = Table.RemoveColumns(SortOnIndex,{"Start Time", "End Time", "Index"})
in
    RemoveRedundantColumns
 

Attachments

  • Modelo 1_withPowerQuery.xlsx
    31.5 KB · Views: 3
vletm

Line 19 is my mistake, sorry, ends at 23:00

Thanks for checking, I didn't realize

Hugs


GraH - Guido, Good morning.

Thanks for the solution I'm still new to Power Query, I'm learning

Hugs

Decio
 
Hi all,
I got challenged by a colleague to find a non CSE formula solution and wanted to share what I came up with.

=SUMPRODUCT(--((ROUND(MOD($C3+(ROW($A$1:INDEX($A:$A,MIN(24,ROUND(($D3-$C3)*24,0)+1)))-1)/24,1),7))=ROUND(E$2,7)))
 
GraH - Guido, Good morning.

The solution is very good, I liked it a lot, but the SUMPRODUCT function is automatically matrix

For Excel 365 version, you can use the SUM function without pressing CTRL + SHIT + ENTER, but this is a matrix

Hugs

Decio
 
Last edited:
Back
Top