Transposed data with blank spaces and create a Bar Chat without any space

[iansitorus]

Hi MrExcel expert, I really appreciate your help to solve this issue.
I have been working on this issue for a couple of days now and I feel stuck.

Data set: Hours & Date.
Goal:
1. I want to sort the data of "date and hours" which have hours assigned to them, and listed beside of my existing data.
2. Ultimate goal: I want to create a bar chart that represents only the date that has hours assigned to them, without any big gaps in between each bar.

Problem:

1. I found this formula online, but I realized it can't work with date reference and since my"Hours" data is transposed from horizontal data, the formula counts blank as "0" so they still count it as a cell with value (the formula doesn't work).
2. Because the date data is transposed from horizontal data, every blank cell on the reference transposed become 01/00/00
3. I totally don't know the solution to this issue.

Hereby I attached the picture of the data.

Looking forward to the discussion and your solution.
Please don't hesitate to ask me for further clarification if my statement does not give you clarity.

Thanks!

 

[Eric W]

How about:

Book1
	A	B	C	D	E
1	Date	Hours		Date	Hours
2	1/00/00			2/12/2023	800
3	1/00/00			2/19/2023	1016
4	1/00/00			2/26/2023	1080
5	1/00/00			3/5/2023	1320
6	1/00/00			3/12/2023	1320
7	2/12/2023	800
8	1/00/00
9	1/00/00
10	1/00/00
11	1/00/00
12	2/19/2023	1016
13	1/00/00
14	1/00/00
15	1/00/00
16	1/00/00
17	2/26/2023	1080
18	1/00/00
19	1/00/00
20	1/00/00
21	1/00/00
22	3/5/2023	1320
23	1/00/00
24	1/00/00
25	1/00/00
26	3/12/2023	1320
27	1/00/00
28	1/00/00
29	1/00/00
30	1/00/00
Sheet4

Cell Formulas
Range	Formula
D2:E6	D2	=FILTER(A2:B30,B2:B30>0)
Dynamic array formulas.