How do I create excel cells to work like MS PROJECT

okay.

Here's what i'm troubled over. This is my last week of attachment and I got this request from company.

I do have a timeline graph which has Start Time & End Time.
Basically, I have no idea how do I make my excel auto format those cells into coloured cells when I typed in the start time and end time.

Lets say, I type in value:
12/3 12:00 (Start time , A column )
12/3 18:00 (End time, B column)

how do i make my cells auto format 6 cells in a roll into coloured cells.

I have attach my file example. the timeline was manually formatted n merge myself. Is there any way to format automatically with codings?

I've though of using formulae to calculate end time - start time, round off to no. of hours.
Then using no. of hours to format cells.

PLEASE HELP T.T

Here's what I did (see attachment). In row 4 at hour 1 of every day, I put the date and formatted it to m/d.
Select your working range (i.e from D8:DG50) and go to conditional formatting>Use a formula
=AND(COLUMN(D8)>=MATCH(INT($B$8),$4:$4,0)+HOUR($B$8)-1,COLUMN(D8)<=MATCH(INT($C$8),$4:$4,0)+HOUR($C$8)-2)

Explaination
MATCH(INT($B$8),$4:$4,0) returns the column which matches the date in your beginning cell (B8)
)+HOUR($B$8)-1 adds the number of cells to bring to the hour
Same explaination for MATCH(INT($C$8)..... except to end
AND(COLUMN(D8)> Beginning Cell/column,COLUMN(D8)<= Ending Cell/Column will turn blue.

Does this work for you?

Of course, this doesn't work like MS Project which is a very complex program, but it does color in timelines.

Originally Posted by ChemistB

Here's what I did (see attachment). In row 4 at hour 1 of every day, I put the date and formatted it to m/d.
Select your working range (i.e from D8:DG50) and go to conditional formatting>Use a formula
=AND(COLUMN(D8)>=MATCH(INT($B$8),$4:$4,0)+HOUR($B$8)-1,COLUMN(D8)<=MATCH(INT($C$8),$4:$4,0)+HOUR($C$8)-2)

Explaination
MATCH(INT($B$8),$4:$4,0) returns the column which matches the date in your beginning cell (B8)
)+HOUR($B$8)-1 adds the number of cells to bring to the hour
Same explaination for MATCH(INT($C$8)..... except to end
AND(COLUMN(D8)> Beginning Cell/column,COLUMN(D8)<= Ending Cell/Column will turn blue.

Does this work for you?

Thanks, this is what I'm looking for.
I got it to work. But from my timeline, my timeline starts from 5.

It seems like when i type in:
Start Time: 23/8 5am
End Time: 24/9 9am

The cell timeline shows:
Start TIme: 23/8 10am
End Time: 24/9 2pm

It seems like it move 5 blocks in advance.

And what is the meaning of =MATCH(INT($B$8),$4:$4,0)
The bold one.

Going to your second question first; The Zero in the MATCH statement tells Excel to look for an exact match.
The formulas are set up so that first they find where the date is in row 4 and that's the starting column. Then they add the number of hours in your starting time (in C8 for example). So if you put the date at hour 5 of the day, it then adds 9 to that column and brings you over too far. Simplist is to start each day at hour 1. If you want to hide the first 5 hours, you can hide those columns and Excel will still do the calculations right.

[message delete]