• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

Excel - create gantt time chart from 4 columns of dates & times

Attached is a doc with 4 colums
C1 - Start date
C2 - End Date
C3 - Start Time
C4 - End Time

I am recording time in 15 minute increments for a deployment plan and would like to fill in the cells in each row according to the date and time data in the 4 columns. There is a 15-minute increment across the the top (x-axis). Can you help me understand how to have cells in this spreadsheet "fill in" based upon the time/date data?

Any help is appreciated!

Thank you, in-advance!
JohnD
TaskTimes.xlsx
0
John Darby
Asked:
John Darby
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

change the values in row 2 to include date and time. You can format them to show only the time, but let them contain the date, too.  Then you can use conditional formatting to highlight the cells.

- Select  the range $H$4:$GC$83
- on the Home ribbon click Conditional formatting > New rule > Use a formula to determine ...
- enter this formula

=AND(H$2>=$C4+$E4,H$2<=$D4+$F4)

mind the placement of the $ signs. Set a format, like a fill color and hit OK.

See attached.

cheers, teylyn
TaskTimes.xlsx
0
 
John DarbyPMAuthor Commented:
Thank you teylyn! This is so straightforward; much appreciated! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now