Hi Carim,
First of, I would like to apologize as my sample file and desired output was presented wrongfully. I have attached the updated file with changes on the desired output. No need to display the break down of each event but just a total of per interval per day.
Secondly, the first table (A:E) is part of a pivot table, so number of days may really depend on the output of the original data source.
I really appreciate the help here!
If we base it on the updated file I sent before, can you show in the formula how it will look up from source file (if possible, in vertical format)? In that way, I can also extend to the next column (for the next day).
Thanks!
Hi Carim,
I've been trying but I can't seem to incorporate your generic formula. I've been playing with "IF" formula, but I'm going nowhere.
Thanks!
Hi Carim,
There is no limit on the number of events. It depends on the number of volunteers on any given day, any time of the day
Thanks!
Hi Carim,
Number of days is moving.
Thanks!
Hi Carim,
Is the formula good only if hours are not blank or zero in the source file? How do we go about if we consider the blanks since the source file will have multiple dates?
Sample: for the line, 8:00am-9:45am, Dec3 is blank but it has entry for Dec6.
Thanks!
Hi Carim,
I just downloaded your test file, and in a glimpse, I felt relieved. Will try it on a bigger data. By the way, for the first line, 9:12am-11:00am, the 0:18 should be under 9:00am interval.
Thanks! ?
Hello Experts!
Another inquiry for time distribution in Excel. If I have a 30-minute intervals, how can I distribute the minutes if I have a total of 63mins with start time of 9:12am and End time of 10:15am. Please note that this is just a sample. Manually, it should be distributed in the below intervals:
9:00am 18 minutes
9:30am 30 minutes
10:00am 15 minutes
Thanks in advance for the help!
Hi Carim,
Adding 0.0000001 resolved it and I applied it to all rows for uniformity. I just really find it weird that in my mobile, the original formula works fine to all rows. Anyways, I thank you so much for your time and effort. I really appreciate!
Thank you again!
Hi Carim,
Here's the sample file.
Hi Carim,
I created a new file to see if that will resolve, unfortunately, it is still the same. I checked the file I created in my mobile Excel and it is calculating correctly. Any idea what could be the issue? Still the same error at 11:30am interval.
Hi Carim,
I tried several times earlier, different data, all giving me error in 11:30am interval. But when I tried it in my mobile Excel, all calculated correctly. It's weird but big thanks!
Hello Experts!
I am trying to breakdown the number of people per shift into interval of 30mins. I have the formula "=SUMIFS(D$2:D$71,$B$2:$B$71,"<="&$F4,$C$2:$C$71,">"&$F4)" in column "G". Everything is calculating correctly except for 11:30am interval. The first 3 columns below are the source that is being distributed into 30-minute intervals. The 5th column (HC) is where the formula is set. The set of numbers (in ladder view) are the manual distribution I made to check if the formula is working ok. All rows are calculating correctly except for the 11:30am interval. I've been checking and I cannot see the error in my formula. All insights will be greatly appreciated. Thanks!
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 113"] [/TD]
[TD="width: 127"] [/TD]
[TD="width: 94"] [/TD]
[TD="width: 50"] [/TD]
[TD="width: 81"]Time[/TD]
[TD="width: 51"]HC[/TD]
[TD="width: 51"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"] [/TD]
[TD="width: 28"]Manual Total[/TD]
[TD="width: 111"] [/TD]
Start Time
[/td]End Time
[/td]HC
[/td]
[TD="align: right"]6:00 AM[/TD]
0
[/td]0
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]7:00:00 AM[/TD]
[TD="align: right"]4:00:00 PM[/TD]
14
[/td]
[TD="align: right"]6:30 AM[/TD]
0
[/td]0
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]7:30:00 AM[/TD]
[TD="align: right"]4:30:00 PM[/TD]
1
[/td]
[TD="align: right"]7:00 AM[/TD]
14
[/td]14
[/td]14
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]8:00:00 AM[/TD]
[TD="align: right"]5:00:00 PM[/TD]
1
[/td]
[TD="align: right"]7:30 AM[/TD]
15
[/td]14
[/td]1
[/td]15
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]8:30:00 AM[/TD]
[TD="align: right"]5:30:00 PM[/TD]
6
[/td]
[TD="align: right"]8:00 AM[/TD]
16
[/td]14
[/td]1
[/td]1
[/td]16
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]9:30:00 AM[/TD]
[TD="align: right"]6:30:00 PM[/TD]
1
[/td]
[TD="align: right"]8:30 AM[/TD]
22
[/td]14
[/td]1
[/td]1
[/td]6
[/td]22
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[TD="align: right"]7:00:00 PM[/TD]
8
[/td]
[TD="align: right"]9:00 AM[/TD]
22
[/td]14
[/td]1
[/td]1
[/td]6
[/td]22
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]10:30:00 AM[/TD]
[TD="align: right"]7:30:00 PM[/TD]
5
[/td]
[TD="align: right"]9:30 AM[/TD]
23
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]23
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD="align: right"]8:00:00 PM[/TD]
3
[/td]
[TD="align: right"]10:00 AM[/TD]
31
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]31
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]11:30:00 AM[/TD]
[TD="align: right"]8:30:00 PM[/TD]
6
[/td]
[TD="align: right"]10:30 AM[/TD]
36
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]36
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]9:00:00 PM[/TD]
4
[/td]
[TD="align: right"]11:00 AM[/TD]
39
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]3
[/td]39
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]12:30:00 PM[/TD]
[TD="align: right"]9:30:00 PM[/TD]
1
[/td]
[TD="align: right"]11:30 AM[/TD]
39
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]3
[/td]6
[/td]45
[/td]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD="align: right"]10:00:00 PM[/TD]
5
[/td]
[TD="align: right"]12:00 PM[/TD]
49
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]3
[/td]6
[/td]4
[/td]49
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]2:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]
3
[/td]
[TD="align: right"]12:30 PM[/TD]
50
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]3
[/td]6
[/td]4
[/td]1
[/td]50
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]2:30:00 PM[/TD]
[TD="align: right"]11:30:00 PM[/TD]
1
[/td]
[TD="align: right"]1:00 PM[/TD]
55
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]3
[/td]6
[/td]4
[/td]1
[/td]5
[/td]55
[/td]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[TD="align: right"]1/1/1900[/TD]
13
[/td]
[TD="align: right"]1:30 PM[/TD]
55
[/td]14
[/td]1
[/td]1
[/td]6
[/td]1
[/td]8
[/td]5
[/td]3
[/td]6
[/td]4
[/td]1
[/td]5
[/td]55
[/td]
[TD="align: center"]TRUE[/TD]
[/TABLE]
That will be GMT +8:00 (14 hours from Central Time and 13 hours during daylight. I wonder if we can have macro as there will be times that i will be converting 2 weeks schedule and about 50 or more names.
no codes yet, i just used manual formual but i'm hoping to get help as well for that since i'll be working with several sets of names.
Hi Pike,
The table is after conversion from another time zone. I have attached a sample spreadsheet.
i tried the code you have provided and it deleted all entries except the first row.
Hello!
I've been working on converting table of schedules, and apparently after conversion, some dates are missing and/or has double entries. Sample table showed double entries for March 14 and missing Mar 19. Is it possible to have a code to 1) at least highlight the duplicate entries so I can confirm which one to keep, and 2) to insert if there are missing date/s (at any given period) - dates should be sequentially complete. [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 64"]Emz[/TD]
[TD="width: 64"]3/12/2018 05:00:00[/TD]
Emz
[/td]3/13/2018 05:00:00
[/td]Emz
[/td]3/14/2018 05:00:00
[/td]Emz
[/td]3/14/2018 Off
[/td]Emz
[/td]3/15/2018 Off
[/td]Emz
[/td]3/16/2018 20:00:00
[/td]Emz
[/td]3/17/2018 20:00:00
[/td]Emz
[/td]3/18/2018 Off
[/td]Emz
[/td]3/20/2018 05:00:00
[/td]Emz
[/td]3/21/2018 05:00:00
[/td]Emz
[/td]3/22/2018 05:00:00
[/td]Emz
[/td]3/23/2018 05:00:00
[/td]
[/TABLE]
Thanks in advance!
Marvhielnx
@nyatiaju - got the file and i can see now that the function is working. Just another thing, upon conversion, Mar 14 has 2 entries and Mar 19 is missing. Is it possible to have a "checker" to check if dates are sequentially complete and no duplicate dates per name, given that I have several names with schedule?
I'm trying to convert a table of schedule from one Central Time timezone to another timezone, GMT +8:00. There's a part that when i convert series of schedules, some day/s is/are missing. First table is the original schedule in Central Time. Second table is my helper columns - the concatenated format, date and time. Third table is the converted schedule.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 86"]Central Time[/TD]
[TD="width: 65"] [/TD]
[TD="width: 65"] [/TD]
[TD="width: 71"] [/TD]
Name
[/td]Date
[/td]start time
[/td]end time
[/td]Walter
[/td]
[TD="align: right"]11-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
Walter
[/td]
[TD="align: right"]12-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
Walter
[/td]
[TD="align: right"]13-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
Walter
[/td]
[TD="align: right"]14-Mar[/TD]
Off
[/td]Off
[/td]Walter
[/td]
[TD="align: right"]15-Mar[/TD]
Off
[/td]Off
[/td]Walter
[/td]
[TD="align: right"]16-Mar[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]3:00 PM[/TD]
Walter
[/td]
[TD="align: right"]17-Mar[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]3:00 PM[/TD]
Walter
[/td]
[TD="align: right"]18-Mar[/TD]
Off
[/td]Off
[/td]Walter
[/td]
[TD="align: right"]19-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
Walter
[/td]
[TD="align: right"]20-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
Walter
[/td]
[TD="align: right"]21-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
Walter
[/td]
[TD="align: right"]22-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[/TABLE]
Table 2 [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 158"]CT[/TD]
[TD="width: 137"] [/TD]
03/11/2018 3:00 PM
[/td]3/12/2018 12:00 AM
[/td]03/12/2018 3:00 PM
[/td]3/13/2018 12:00 AM
[/td]03/13/2018 3:00 PM
[/td]3/14/2018 12:00 AM
[/td]03/14/2018 Off
[/td]03/14/2018 Off
[/td]03/15/2018 Off
[/td]03/15/2018 Off
[/td]03/16/2018 6:00 AM
[/td]03/16/2018 3:00 PM
[/td]03/17/2018 6:00 AM
[/td]03/17/2018 3:00 PM
[/td]03/18/2018 Off
[/td]03/18/2018 Off
[/td]03/19/2018 3:00 PM
[/td]03/20/2018 12:00 AM
[/td]03/20/2018 3:00 PM
[/td]03/21/2018 12:00 AM
[/td]03/21/2018 3:00 PM
[/td]03/22/2018 12:00 AM
[/td]03/22/2018 3:00 PM
[/td]3/23/2018 12:00 AM
[/td]
[/TABLE]
Table 3 [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 159"]GMT 8:00[/TD]
[TD="width: 135"] [/TD]
[TD="align: right"]3/12/2018 5:00 AM[/TD]
[TD="align: right"]3/12/2018 2:00 PM[/TD]
[TD="align: right"]3/13/2018 5:00 AM[/TD]
[TD="align: right"]3/13/2018 2:00 PM[/TD]
[TD="align: right"]3/14/2018 5:00 AM[/TD]
[TD="align: right"]3/14/2018 2:00 PM[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]3/16/2018 8:00 PM[/TD]
[TD="align: right"]3/17/2018 5:00 AM[/TD]
[TD="align: right"]3/17/2018 8:00 PM[/TD]
[TD="align: right"]3/18/2018 5:00 AM[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]3/20/2018 5:00 AM[/TD]
[TD="align: right"]3/20/2018 2:00 PM[/TD]
[TD="align: right"]3/21/2018 5:00 AM[/TD]
[TD="align: right"]3/21/2018 2:00 PM[/TD]
[TD="align: right"]3/22/2018 5:00 AM[/TD]
[TD="align: right"]3/22/2018 2:00 PM[/TD]
[TD="align: right"]3/23/2018 5:00 AM[/TD]
[TD="align: right"]3/23/2018 2:00 PM[/TD]
[/TABLE]
Need help on the following:
First, if it's a scheduled off, it should show date + off. Next, it should show sequential dates when converted. In the sample above, after Mar 17, it should show Mar 18 and 19, but they are missing.
Any help is greatly appreciated.
Thanks
Marvhielnx
