blah blah blah is here! blah blah » Close

0
andy_olsen
60
330 views

Datetime problems

up0down
link

Hi all

I'm doing a web application in VS2008 with Sql server database, and I do not seem to find a solution to my problem.
I have two textboxes where I enter from date and to date, and when I push the button it will populate a couple of labels with information like how many hits and total time. The employee enter his working hours in this system and it will be saved to the database. (For the employee it looks like this: Begin at work: 2010-04-08 07:00:00 and end of work: 2010-04-08 17:00:00 then he saves it).
When I search between let's say 1 march to 31 march then I will see the total hours for 1 person and how many days he has worked in that range.

Is there a way to handle overtime, if this person works between 6 pm and 6 am I need to populate a third label with this hours. Cause this will give him more money per hour.

And if he works on weekends I need to populate a fourth label with this information.

I use a datareader to get the total hours and how many days, is there a way to write code that will get this information as well? I would rather let the program do this then having the employees doing it. The less manual work the less errors I believe.

datetime sql asp.net
  • asked one year ago
add comment
last answered one year ago

3 answers

Sort:
up0down
link

Hi again

I got no responses from anyone, is my question unclear or is it too hard to manage?
Please comment so I know...

add comment
up0down
link

If you had two more textboxes where the user enters 'overtime from date' and 'overtime to date', then you could populate the third label with the total overtime.

It would be possible to deduce how much of his/her 'normal' time or overtime is worked at weekends from the dates entered in the textboxes so you don't really need to get any additional input from the user to cover this point.

It's possible to work out programatically how much time has been worked using the DateTime and TimeSpan classes. Here's some 'rough and ready' code - in practice, you'd need to add code to check for invalid or unlikely dates:

string format = "yyyy-MM-dd HH:mm:ss";

DateTime normalFrom = DateTime.ParseExact(textBox1.Text, format, null);
DateTime normalTo = DateTime.ParseExact(textBox2.Text, format, null);
DateTime overtimeFrom = DateTime.ParseExact(textBox3.Text, format, null);
DateTime overtimeTo = DateTime.ParseExact(textBox4.Text, format, null);

double normalHours = (normalTo - normalFrom).TotalHours;
if (normalHours < 0.0) normalHours = 0.0;
int normalDays = (int)normalHours / 24;
normalHours -= normalDays * 24.0;

double overtimeHours = (overtimeTo - overtimeFrom).TotalHours;
if (overtimeHours < 0.0) overtimeHours = 0.0;
int overtimeDays = (int)overtimeHours / 24;
overtimeHours -= overtimeDays * 24.0;

label2.Text = String.Format("{0} days {1} hrs", normalDays, normalHours);
label3.Text = String.Format("{0} days {1} hrs", overtimeDays, overtimeHours);

I haven't done anything about weekends at this stage in case I haven't understood the question correctly :)

  • answered one year ago by:

    vulpes
    17279
  • edited one year ago
(x)
andy_olsen
60

Thank You Vulpes. It can be hard to explain sometimes, basically I just want a person to enter his time when beginning at work and when ending the work. If this person end work later than 6 pm or begin before 6 am this is overtime and I want that separatly. If this person works at a weekend it is also overtime but with different pay. That is why I need to separate the two overtimes, and my thought was that this person should only enter his time when beginning at work and ending work to minimize errors. If this person must enter time, overtime and weekend overtime the possibility of human errors increase...let the program handle it instead...

add comment
up0down
link

OK, I think I'm clear now what you're trying to do :)

The code to do this is rather involved, and I haven't had time to test it thoroughly, but try the following:

I've assumed that nobody will work continuously for more than 24 hours and so the 'shift' will either end on the same day or the next day:

string format = "yyyy-MM-dd HH:mm:ss";
DateTime from = DateTime.ParseExact(textBox1.Text, format, null);
DateTime to = DateTime.ParseExact(textBox2.Text, format, null);

double normal = (to - from).TotalHours;
double overtime = 0.0;
double weekend = 0.0;

if (normal < 0.0)
{
    normal = 0.0;
}
else
{
    switch (from.DayOfWeek)
    {
        case DayOfWeek.Saturday:

            weekend = normal;
            normal = 0.0;
            break;

        case DayOfWeek.Sunday:

            if (to.DayOfWeek == DayOfWeek.Sunday)
            {
                goto case DayOfWeek.Saturday;
            }
            weekend = (to.Date - from).TotalHours;
            normal -= weekend;
            if (normal > 18.0)
            {
                overtime = normal - 12.0;
                normal = 12.0;
            }
            else if (normal > 6.0)
            {
                overtime = 6.0;
                normal -= 6.0;
            }
            else
            {
                overtime = normal;
                normal = 0.0;
            }
            break;

        case DayOfWeek.Friday:

            if (to.DayOfWeek == DayOfWeek.Saturday)
            {
                weekend = (to - to.Date).TotalHours;
                normal -= weekend;
                if (normal > 18.0)
                {
                    overtime = normal - 12.0;
                    normal = 12.0;
                }
                else if (normal > 6.0)
                {
                    overtime = 6.0;
                    normal -= 6.0;
                }
                else
                {
                    overtime = normal;
                    normal = 0.0;
                }
            }
            else
            {
                goto default;
            }
            break;

        default:

            if (from.DayOfWeek == to.DayOfWeek)
            {
                if (from.Hour < 6)
                {
                    if (to.Hour < 6)
                    {
                        overtime = normal;
                        normal = 0;
                    }
                    else if (to.Hour < 18)
                    {
                        overtime = (from.Date.AddHours(6) - from).TotalHours;
                        normal -= overtime;
                    }
                    else
                    {
                        overtime = normal - 12.0;
                        normal = 12.0;
                    }
                }
                else if (from.Hour < 18)
                {
                    if (to.Hour >= 18)
                    {
                        overtime = (to - from.Date.AddHours(18)).TotalHours;
                        normal -= overtime;
                    }
                }
                else
                {
                    overtime = normal;
                    normal = 0.0;
                }
            }
            else
            {
                double temp = (to.Date - from).TotalHours;
                if (temp > 18.0)
                {
                    overtime = temp - 12.0;
                }
                else if (temp > 6.0)
                {
                    overtime = 6.0;
                }
                else
                {
                    overtime = temp;
                }
                normal -= overtime;

                temp = (to - to.Date).TotalHours;
                if (temp > 18.0)
                {
                    overtime += temp - 12.0;
                    normal -= temp - 12.0;
                }
                else if (temp > 6.0)
                {
                    overtime += 6.0;
                    normal -= 6.0;
                }
                else
                {
                    overtime += temp;
                    normal -= temp;
                }
            }
            break;
    }
}
           
label2.Text = String.Format("Normal   : {0:F3} hours", normal);
label3.Text = String.Format("Overtime : {0:F3} hours", overtime);
label4.Text = String.Format("Weekend  : {0:F3} hours", weekend);

  • answered one year ago by:

    vulpes
    17279
(x)
andy_olsen
60

Thank you very much, I will try this right away. Yes correct, nobody will work as much as 24 hours at one time. But they do work at various times.

add comment
Feedback