0

I want to validate a column in Excel using EPPlus for time range like 1:00 PM to 11:00 AM.

Time must be "hh:mm AM/PM" format.

I solved the problem. Here is the code : 

            var validationEndTime = workSheet.DataValidations.AddTimeValidation("H:H");
            validationEndTime.ShowInputMessage = true;
            validationEndTime.Prompt = "Add end time in hh:mm AM/PM format as example 9:00 AM or 12:30 PM";
            validationEndTime.ErrorStyle = ExcelDataValidationWarningStyle.stop;
            validationEndTime.ShowErrorMessage = true;
            validationEndTime.Error = "Insert valid time";
            validationStartDate.Operator = ExcelDataValidationOperator.between;
            var timeEnd = validationStartDate.Formula.Value;
            timeEnd.Hour = 00;
            timeEnd.Minute = 00;
            var timeEnd2 = validationStartDate.Formula2.Value;
            timeEnd2.Hour = 23;
            timeEnd2.Minute = 59;
            validationEndTime.Formula.Value = timeEnd;
            validationEndTime.Formula2.Value = timeEnd2;

And this will make a validation in excel like this :

enter image description here

hasnayn
  • 356
  • 4
  • 22

1 Answers1

2

I tried a lot to make DataValidations.AddDateTimeValidation work but it didn't work for me. So instead of looking for solution in C#, I tried to figure out how excel deals with time.

Excel stores time in decimal numbers. So instead of using DataValidations.AddDateTimeValidation, I used DataValidations.AddDecimalValidation, I converted the start time and end time in excel to decimal number with 10 digit precision.

So 12:00:00 just becomes 0.00 and 11:59:59 pm becomes 0.999305555555556.

My C# code looks something like this

    var validdateTime = sheet1.DataValidations.AddDecimalValidation("H:H");
    validdateTime.ShowInputMessage = true;
    validdateTime.Prompt = "Add end time in hh:mm AM/PM format as example 9:00 AM or 12:30 PM";
    validdateTime.AllowBlank = false;
    validdateTime.ShowErrorMessage = true;
    validdateTime.Error = "The time entered is not valid";
    validdateTime.ErrorStyle = ExcelDataValidationWarningStyle.stop;
    validdateTime.Operator = ExcelDataValidationOperator.between;
    validdateTime.Formula.Value = 0.0;
    validdateTime.Formula2.Value = 0.999305555555556;
Ashish
  • 21
  • 3