Create list from cells with data ? please help

[InstructorAmberA]

Good afternoon Excel gurus I am hoping you can help me with a time robber of a spreadsheet. I am still learning excel so if the answer is simple, forgive me. I have tried arrays, and lookups, and offsets and small and well.. i think i have confused myself into going back to manual solutions.

The chart below is a snip of a report I get from my scheduling program that says which facilities in which state have a coverage gap. I then take the gaps and reach out to the remote employees to see who can fill the gaps. At this time, I manually take every Zero and translate it to create the shortage/gap list for the entire month.
Raw report has data in 1800 rows, and Columns A-CT. Schedule is 3 months at a time.
With the hundreds of facilities, and dates and times, it takes me days to transcribe the dates and times manually. The report names the locations by State / Facility Name / Facility ID if that is helpful. The Dates and times are listed as shown below but it also gives a combined column if needed formatted like this: 2018-09-29, 10

Manually translatting each 0:
9/27/18 9-10am NC898
9/27/18 11am-1pm TX235
9/27/18 1pm - 3pm all NC Facilities

The end format look can be whatever is easiest to spit out. I would greatly appreciate a way to get this done in less time than it takes me currently. I am hoping to create a template to paste the report into and it just give me those manual results above. (they can be in multiple cells if easier)
Thanks so much!

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]TX235[/TD]
[TD]TX236[/TD]
[TD]NC897[/TD]
[TD]NC898[/TD]
[TD]NC899[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl63, width: 75, align: right"]9/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl63, width: 75, align: right"]9/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl63, width: 75, align: right"]9/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl63, width: 75, align: right"]9/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl63, width: 75, align: right"]9/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl63, width: 75, align: right"]9/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[dfsobral]

Let me see if I understand.

In cell F2 you want to change
- 0
to
- 9/27/18 9-10am NC898

If so is the information of the time in column B? For example if in cell B2 is 10 this mean 9-10am?

If so in your second example shouldn't be
- 9/27/18 10am-12pm TX235
instead your example of
- 9/27/18 11am-1pm TX235

 

[InstructorAmberA]

@dfsobral - you are correct - my error there. The examples should read:
Manually translatting each 0:
9/27/18 10am-11am NC898
9/27/18 11am-1pm TX235
9/27/18 1pm - 3pm all NC Facilities

Which is exactly why automation of this would be amazing. Look at it for 6 hours straight and the lines all blur together. Thanks for the correction
Amber

 

[dfsobral]

This is not an ideal solution but it will help

try running this macro assuming the table range is A1:CT1800, columns A is date, B is hour and line 1 is facility

Sub macro()
Dim rng as Range
Dim rng2 as Range

Application.ScreenUpdating=False
Set rng=Range("C2:CT1800")
rng.Replace 0, "#"
For Each rng2 in rng
If rng2="#" then
rng2.FormulaR1C1 = "=MONTH(RC2)&""/""&DAY(RC2)&""/""&YEAR(RC2)&"", ""&RC3-1&""-""&RC3&"", ""&R9C"
rng2.Value = rng2
End If
Next rng2
Application.ScreenUpdating=True

MsgBox(Finished")

End Sub