• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to restrict cell entry to date and time in excel 2010

O

online99

New Member
Hi,

I'm doing duration computations that require cell input in the (mm/dd/yyyy hh:mm) format. I know I can use data validation to require a date or a time to be entered, but I can't figure out if there is a way to insure that date and time are entered.


Does anyone have any suggestions?


Thank you
 
I think your best bet is to use 2 separate cells for date and time. The problem with putting everything in one cell would be in the fact that XL treats dates and time as integers and decimals respectively. So, while we could check a number to make sure there's a decimal portion, this would have the downside of not allowing a time of 12:00 AM, since that has a value of 0. If you know for sure that won't be a problem, I suppose you could do a validation check like this:


=AND(ISNUMBER(A2),A2>0,MOD(A2,1)>0)


With the 3 portions of the AND checking to make sure a) that it's a number/date and not text, b) it's a positive number since dates can't be negative, and c) there's a decimal portion of the numer, aka some time value.
 
I agree that it would be the easiest way to handle it. However, I use this input with the formula below. The networkdays function uses date and time in a single cell. So it's more important to me to have the data in a single cell than to have validated separate cells.


I thought about maybe trying to concatenate the date and time, but haven't experimented to see if that produces something compatible with networkdays.


I need to think through the formula you posted above, to see if I might be able to apply the concept. At the moment, I'm not seeing it, but I could just be slow.


Thanks


=IF(ISBLANK(L3),

"",

IF(ISBLANK(O3),

"Not Fixed",

IF(ISBLANK(M3),

(NETWORKDAYS(L3,O3,Hidden!$A$1:$A$50)-1)*(Hidden!$G$1-Hidden!$F$1)+IF(NETWORKDAYS(O3,O3,Hidden!$A$1:$A$50),MEDIAN(MOD(O3,1),Hidden!$G$1,Hidden!$F$1),Hidden!$G$1)-MEDIAN(NETWORKDAYS(L3,L3,Hidden!$A$1:$A$50)*MOD(L3,1),Hidden!$G$1,Hidden!$F$1),

(NETWORKDAYS(M3,O3,Hidden!$A$1:$A$50)-1)*(Hidden!$G$1-Hidden!$F$1)+IF(NETWORKDAYS(O3,O3,Hidden!$A$1:$A$50),MEDIAN(MOD(O3,1),Hidden!$G$1,Hidden!$F$1),Hidden!$G$1)-MEDIAN(NETWORKDAYS(M3,M3,Hidden!$A$1:$A$50)*MOD(M3,1),Hidden!$G$1,Hidden!$F$1))))
 
You wouldn't concatenate the time and date, just add them together. This will produce the same result as if they were both input into a single cell.

Also, hate to rain on a complex formula, but NETWORKDAYS function doesn't account for time, it only looks at the date/integer portion. =(
 
Great answer!


As far as the formula goes, I thought at the time I did this that I had found a way to acount for working hours. And I thought I even tested it. But it's long enough ago that I'll have to test again.
 
Ah, I think I see now how it might with the use of the MOD function. So, actually you could have the use input in 2 cells, add the values, then use current formula, or leave it as 2 cells since technically your formula is having to calculate the 2 components separately anyway. Up to you.
 
You must log in or register to reply here.
Back
Top