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!

	A	B	C	D	E	F	G
1			TX235	TX236	NC897	NC898	NC899
2	9/27/2018 <tbody> </tbody>	10				0
3	9/27/2018 <tbody> </tbody>	11	0
4	9/27/2018 <tbody> </tbody>	12	0
5	9/27/2018 <tbody> </tbody>	13			0	0	0
6	9/27/2018 <tbody> </tbody>	14			0	0	0
7	9/27/2018 <tbody> </tbody>	15			0	0	0

<tbody>
</tbody>

 

[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