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.

 

[ryangordon]

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 it.I remember even trying to find something relevant about Julius Caesar,but all i was getting was trash information,only relevant essay was on this link.

Thank you.

View attachment 84374

1. Open your Excel workbook and press Alt+F11 to access the Visual Basic Editor.
2. Inside the editor, click on "Insert" and choose "Module" to create a new module.
3. Copy and paste the following code into the module window:

vbaCopy code

>>> use code - tags <<<

Function FormatGanttCell(startDate As Date, duration As Integer, targetCell As Range)
    ' This function formats Gantt chart cells based on the start date, duration, and target cell reference.
 
    Dim fullSlots As Integer
    Dim partialSlot As Double
    Dim currentDate As Date
 
    ' Calculate the number of full 8-hour slots.
    fullSlots = duration \ 8
 
    ' Calculate the fractional part of the last slot.
    partialSlot = (duration Mod 8) / 8
 
    ' Check if the start date falls on a weekend and adjust accordingly.
    If Weekday(startDate) = 1 Then
        startDate = startDate + 1 ' Move to Monday if the start date is Sunday.
    ElseIf Weekday(startDate) = 7 Then
        startDate = startDate + 2 ' Move to Monday if the start date is Saturday.
    End If
 
    ' Apply formatting to the target cell and subsequent partial slot cell(s).
    For i = 0 To fullSlots
        currentDate = startDate + i
        targetCell.Offset(i, 0).Interior.ColorIndex = 6 ' Fill the full slot cell with color.
    Next i
 
    ' Apply formatting to the partial slot cell if necessary.
    If partialSlot > 0 Then
        targetCell.Offset(fullSlots, 0).Interior.Pattern = xlPatternLinearGradient
        targetCell.Offset(fullSlots, 0).Interior.Gradient.Degree = 90 ' Apply a gradient pattern to the partial slot cell.
        targetCell.Offset(fullSlots, 0).Interior.Gradient.ColorStops.Add(0).Color = RGB(255, 255, 255) ' Start color.
        targetCell.Offset(fullSlots, 0).Interior.Gradient.ColorStops.Add(partialSlot).Color = RGB(255, 255, 255) ' End color.
    End If
End Function

1. Save the changes and close the Visual Basic Editor.
2. Go back to your Excel worksheet.
3. Select the cell where you want the Gantt chart to start.
4. Open the "Conditional Formatting" menu from the Excel ribbon.
5. Choose "New Rule" and select "Use a formula to determine which cells to format."
6. In the formula field, enter the following formula:
   bashCopy code
   =FormatGanttCell($A2, $C2, B2)
   Note: Adjust the cell references in the formula based on your specific data.
7. Click on the "Format" button to define the desired formatting options, such as fill color.
8. Click "OK" to apply the conditional formatting rule.

Now, whenever you change the start date or duration values, the function will be triggered by the conditional formatting rule and format the corresponding cells in the Gantt chart accordingly.

Remember to save your workbook in a macro-enabled format (e.g., .xlsm) to ensure that the VBA code is preserved.
if you have any questions feel free to ask!