[Solved] How to write conditional formatting with respect to date and time together?

[Leela Prasad]

Hi All,

I want to write a function in Excel. I have prepared a Gantt chart with dates as a row header. I have to fill in the task's start date and end date and each task has a particular duration. I wanted to split each day into 3 slots(8 hours each). For example, if a task has a start date of 09-06-2023 and it will take 32 hours to complete the task, then it should fill the cell under 09-06-2023 completely and the cell under 10-06-2023 partially (1/3rd part of a cell) also ignore weekends.

I wanted to write a function because I have linked my Excel with power automate and I see power automate doesn't support .xlsm format. I have attached a pic for your reference.

Please help me to write a function in conditional formatting.

Thank you.

 

[Rubayed Razib]

Greetings Prasad,
Unfortunately, it is not possible in an Excel worksheet to fill a cell partially in three separate parts using Conditional Formatting. You only can do it using two separate sets of colors or patterns. For this, you might need VBA to resolve this. But as you mentioned, VBA is not a viable solution in your case. Its better to try to incorporate your file with the power automate as we know it can be loaded with the power automate.
I have added a sample example where you can see how we can add two separate conditional formatting to format cell values in Gantt chart.
=AND(AND($B55<=F$3,F$3<=$C$5),$E$5=(1+INT(F$3-$B$5)*2),WEEKDAY(F$3,2)<=5)
=AND(AND($B55<=F$3,F$3<=$C$5),$E$5>=(2+INT(F$3-$B$5)*2),WEEKDAY(F$3,2)<=5)
both of this formula need to be applied across all of the cell of gantt chart.
You can use both of these formulas and set the formatting in the formatting tab.
Here you can have two slots per day as there is no other way to visualise 3 slot per day in a single cell.
A sample dataset is now presented below here. Where we have 2 slot per day and we are able to distribute in two and half a day

If you have other queries, dont hesitate to post your questions again.


Thanks and regards
Rubayed Razib