Conditional Formatting Nightmare

[MattieIJClark]

Hi All,

I'm currently building a time-sheet for a member of my staff and I was hoping I could use conditional formatting so that if the member of staff is on annual leave, sick or is a public holiday the spreadsheet will enter the start and finish times, a time-stamp almost.

I've uploaded a draft copy of my time sheet.

Any help will be greatly appreciated, I no little of VBA, so if this is the route I need to go down please explain in the easiest terms a novice can understand.

 

[PCosta87]

Hi,

Welcome to the forum

As far as I know conditional formatting only does formatting, as the name suggests , so I'm not sure you can have it input any content into the cells.

Have you considered using a formula? I'm sure a simple "IF" will suffice:
Assuming those 1's are check-marks for when to automatically input the time:

In C11 "=IF(SUM(L11:O11)>0,"08:15","")"
In F11 "=IF(SUM(L11:O11)>0,"12:45","")"

Or something similar.

Hope this helps

 

[MattieIJClark]

Hi,

Welcome to the forum

As far as I know conditional formatting only does formatting, as the name suggests , so I'm not sure you can have it input any content into the cells.

Have you considered using a formula? I'm sure a simple "IF" will suffice:
Assuming those 1's are check-marks for when to automatically input the time:

In C11 "=IF(SUM(L11:O11)>0,"08:15","")"
In F11 "=IF(SUM(L11:O11)>0,"12:45","")"

Or something similar.

Hope this helps

Many thanks PCosta87, I did think of that, however is need to be in the background so that if the member of staff is in they can still enter their start and finish times. Hence why I was thinking conditional formatting might work.

Think it'll be a VBA of some description if conditional formatting is a no go.

 

[PCosta87]

I don't think that is a reason to dismiss the option... they can still enter the times as before (it will simply overwrite the formula), unless I'm missing something

 

[MattieIJClark]

Had another look online and you are right PCosta87, I'm going to use your formula and then protect the spreadsheet this way:

format>cells>protection and uncheck locked, then select the formula(s) and do the same but check hidden instead of unchecking locked, then protect the sheet, now you can change the values in the cells you unlocked but they cannot see the formulas only the result

Only issue is if the member of staff accidentally types in their in and out time then that will remove the formula. But it's a work around.

 

[bosco_yip]

Conditional Formatting way :

For the annual leave, sick or public holiday, you still need to enter "0" in the cells of Time-in (Column C) and Time-out (Column F)

The purpose of enter "0" is to active the Conditional Formatting rule, and the cell of Column C and Column F in the leave/holiday rows will change to "8:15" and "12:45" by the Conditional Formatting.

Please check with the Conditional Formatting rule of formula + Format >> Custom cells format as per attachment pictures.

Regards
Bosco

 

[MattieIJClark]

Hi Bosco,

That is brilliant, thank you so much for that.

Next question, how do I get the Total hours column to recognise the conditional formatting time stamps.

Because the still need to bring a cumulative totals through, so I know it'll always be 4 hours and 30 minutes but don't want to have to key this in manually.

Your help is much appreciated.

Thanks

 

[bosco_yip]

Hi Bosco,

That is brilliant, thank you so much for that.

Next question, how do I get the Total hours column to recognise the conditional formatting time stamps.

Because the still need to bring a cumulative totals through, so I know it'll always be 4 hours and 30 minutes but don't want to have to key this in manually.

Your help is much appreciated.

Thanks

1] Adopt the same logic of post #.6 to The hours Column, just modified the Custom Cell Format in the Conditional Formatting.

2] See attached revision

3] Reminding using Conditional Formatting way, the Time is display only, the inlaying number in the above example all are actually "0".

Regards
Bosco

 

[Eloise T]

Conditional Formatting way :

For the annual leave, sick or public holiday, you still need to enter "0" in the cells of Time-in (Column C) and Time-out (Column F)

The purpose of enter "0" is to active the Conditional Formatting rule, and the cell of Column C and Column F in the leave/holiday rows will change to "8:15" and "12:45" by the Conditional Formatting.

Please check with the Conditional Formatting rule of formula + Format >> Custom cells format as per attachment pictures.

Regards
Bosco

How did you put red arrows on the spreadsheet?

 

[SirJB7]

Hi, Eloise T!
From Insert tab, Shapes icon, Lines.
Regards!

 

[Eloise T]

Hi, Eloise T!
From Insert tab, Shapes icon, Lines.
Regards!

THANK YOU!