Clear contents

[Aidan]

Hi all,

I haven't been on for a while (and have forgotten all I have learned from this site) primarily because I haven't been using excel much in my work.

However I have been asked a question by a colleague and need a bit of help in answering it.

My colleague has devised a timesheet with the following columns

Column a = date
column b = da
column c = time in
column d = lunch start
column e = lunch end
column f = time out
column g = hours worked

Basically he is looking for a macro that will check that lunch start is greater than time in, if not then a message box appears saying wrong value and deletes the value in lunch start column.

I have had a quick search of the site but can't find anything that relevant. Your help, as always, would be much appreciated.

Regards


Aidan

 

[Richard Schollar]

Hi Aidan

Just a thought, but wouldn't data validation be a better idea than a macro? It would be a lot simpler.

Richard

 

[Aidan]

Hi Richard,

thanks for that. As I said I don't use excel much now and if you don't use it you forget it quite quickly.

Another point about data validation. lunch start and lunch end must be greater than 30 mins. I have tried greater than d9+30 but doesn't like any of that.

Any ideas


Aidan

 

[Richard Schollar]

Aidan

Validation Criteria:
1. Allow - "Time"
2. Data - "Greater Than"
3. Start Time - "=d9+30/1440"

works for me. 30/1440 is the portion of a whole day that 30 minutes represents.

Best regards

Richard