Data Validation for dates & time exceeding 24hrs

Hi Guys,

I wanted to enter a data validation for a column in a spreadsheet I am designing.

The problem is; if I use the Time in the 'Allow' combo box and then between in the 'Data' combo box, I set the start time as 0:00 and I wanted to set the end time as 999:00 as I am dealing with total hours worked and not hours within 24 hours if you get what I mean.

The problem is sometimes I need to enter large hours such as 521 hours for example.

Is there a way that I can set maybe a wildcard format so that as long as the time format is h:mm it will accept it.

The Time cell is formatted as: [h]:mm.

My second problem is with dates. I have another sheet on the spreadsheet for checking vacancies. in the first column is 'Date from' and the second column is 'Date to' for the required vacancy for temp staff. The third column then calculates how many weeks the temp member of staff is needed by simply subtracting the 'Date from' from 'Date to'.

I wanted a Validation in the 'Date to' column so that if a date is entered that is lower than the 'Date from' date then it will flag up an error as I don't want to be dealing with negative dates.

How can I achieve this?

Thanks in advance for any help offered.

you need to divide the value by 24 such as
=521/24
then you need to custom format the cell
select cell, format,cells,number,custom
enter this in the box

[h]:mm

@Dav

Thanks for your help. The date format works fine for any hours up to 999:00, but for some reason if I enter 0:00 or even 0:01 as a time it says that it is invalid. Any ideas on what's gone wrong?

Thanks.

@davesexcel

I'm not quite sure what you mean. I have already formatted the cell in the custom format of [h]:mm so that I can have total hours greater than 24hrs.

I wanted to restrict users from only being able to enter time from 0:00 to 999:0.

Thanks.