In practical exam, each candidate is tested if he acquires the general computer operation skills. There is one question from MS Excel carrying 10 marks. Thus, it is second largest score after Devnagari typing. In excel question you are expected to have ability to process data using suitable formula and functions apart from general formatting and database management tasks in Excel. Here I present one question and the procedure to solve it. Expect some more to come in future.

You can download this tutorial as PDF if you want to practice while you are offline. Download this tutorial as PDF File

Microsoft Excel Practical Preparation

Question 1:

Calculate the daily and weekly working hours from the data below

Day	Arrival	Lunch Out	Lunch In	Departure	Working Hour
Sunday	7:00	11:00	12:30	17:00
Monday	8:30	12:00	13:00	16:30
Tuesday	7:00	12:00	13:00	16:00
Wednesday	9:00	11:30	12:30	17:00
Thursday	7:00	11:30	12:00	17:00
Friday	7:30	11:00	12:00	16:00
Saturday	0:00	0:00	0:00	0:00
Total Working Hours
Wage Per hour					420
Total Wage

Answer

The arrival, departure and lunch break time are are given from where we need to calculate daily working hour.

Calculate Daily Working Hours

Daily working hour can be calculated in two ways:

1)  Subtract arrival time from departure time to get total daily working hour. Then subtract lunch break time.

To calculate total working hours:
=Departure – Arrival

To calculate Lunch Break:

=Lunch in – lunch out

So, the net working hour daily

= (departure – arrival) – (lunch in – lunch out)

2)  Or you can get working hours before lunch and after lunch then add both of them.

Hours before lunch

= lunch out – arrival

Hours after lunch

= departure – lunch in

So, the net working hour daily

=(lunch out – arrival) + (departure – lunch in)

Following is the worksheet after I calculated daily working hours.

	A	B	C	D	E	F
1	Day	Arrival	Lunch Out	Lunch In	Departure	Working Hour
2	Sunday	7:00	11:00	12:30	17:00	8:30
3	Monday	8:30	12:00	13:00	16:30	7:00
4	Tuesday	7:00	12:00	13:00	16:00	8:00
5	Wednesday	9:00	11:30	12:30	17:00	7:00
6	Thursday	7:00	11:30	12:00	17:00	9:30
7	Friday	7:30	11:00	12:00	16:00	7:30
8	Saturday	0:00	0:00	0:00	0:00	0:00
9	Total Working Hours
10	Wage Per hour					420
11	Total Wage

Formula Used for Working Hour F2

=(C2-B2)+(E2-D2)

This resulted 8 hours 30 minutes

To enter formula for other rows

Select cells F2 to F8 and Press Ctrl+D

[Ctrl+D is the short cut key for Edit >> Fill >> Down]

Calculate Total Working Hours

Calculating total working hours is not as straight as it appears. Thinking straightly, there are hours from Sunday to Saturday, so adding them up should return you the total working hours. But when I did that the value there was:

23:30

What rubbish?

Excel is much smarter and does many things automatically for you. But sometimes if you do not care what is happening, can be a good source of your headache.

How came 23:30 as the sum of those values from Sunday to Saturday?

When adding, excel calculated the sum and converted it into day, hour, minute format. When day is not displayed in your cell, the value 23:30 looks rubbish.

Select the cell F9 and go to Format >> Cells and choose Custom in Number tab. Type d  h:mm

You’ll get the value 1  23:30, which means 1 day 23 hours and 30 minutes. So, there was the data hiding!

But we need to calculate Total Wage and for that you need number of hours, not day, hour & minutes. Lets convert everything into number of hours.

Lets delete the value on F9 and type the new formula below so that we get hours only.

=hour(total of daily working hour) * 24 + hour(total of daily working hour)

	A	B	C	E	D	F
1	Day	Arrival	Lunch Out	Lunch In	Departure	Working Hour
2	Sunday	7:00	11:00	12:30	17:00	8:30
3	Monday	8:30	12:00	13:00	16:30	7:00
4	Tuesday	7:00	12:00	13:00	16:00	8:00
5	Wednesday	9:00	11:30	12:30	17:00	7:00
7	Thursday	7:00	11:30	12:00	17:00	9:30
8	Friday	7:30	11:00	12:00	16:00	7:30
9	Saturday	0:00	0:00	0:00	0:00	0:00
9	Total Working Hours					47.00
10	Wage Per hour					420
11	Total Wage

The formula I used here was: F9

=DAY(SUM(F2:F8))*24+HOUR(SUM(F2:F8))

Calculating Total Wage

There is nothing tricky in Total Wage. You can directly multiply Total Working Hours by Wage Per hour. So following is the final version of my calculation.

Day	Arrival	Lunch Out	Lunch In	Departure	Working Hour
Sunday	7:00	11:00	12:30	17:00	8:30
Monday	8:30	12:00	13:00	16:30	7:00
Tuesday	7:00	12:00	13:00	16:00	8:00
Wednesday	9:00	11:30	12:30	17:00	7:00
Thursday	7:00	11:30	12:00	17:00	9:30
Friday	7:30	11:00	12:00	16:00	7:30
Saturday	0:00	0:00	0:00	0:00	0:00
Total Working Hours					47.00
Wage Per hour					420
Total Wage					19740