Hours and week days

[viangogh]

Hello

I've been trying to find out help without posting, reading other posts
but didn't...

So here goes my problem:
(I only discovered EXCEL last week...!)

I want to create a workbook that calculates my salary automaticaly jus
by entering the day of work and the hour I started and the hour I go
out (I get payed by the day).

1. So, first I need to calculate the TOTAL of hours of work in a give
day knowng the hour I started and the hour I finished.

EX:
A B
08:00 20:00 - Starting hour
22:00 07:00 - Ending hour
14 11 - TOTAL in HOURS (without minutes)

2. Then, here's the tricky part... My salary changes if it is a wee
day, or a weekend day (and holiday).
So, I need EXCEL to know the weekday of the date I insert (ex: I inser
08.03.2004 and EXCEL reconizes it as MONDAY), and from there multipl
the number of HOURS of that day in one of two charts I have in anothe
sheet; one for MON, TUE, WED, THU, FRI, and the other for SAT, SUN an
HOLIDAY...

What I did so far:

I have a 1st sheet like this:

A B
1 DAY Day1
2 DATE 08.03.2004
3 WEEKDAY monday
4 FROM 08:00
5 TO 22:00
6 TOTAL 14

I want to enter B2 and EXCEL imediately fills B3 with the week day.
I want to enter B4 and B5 and EXCEL automaticaly discovers B6 - th
TOTAL of hours (even if it goes over midnight).

Then, already knowing if I worked in a weekday or a weekend day (ar
holidays even possible?), EXCEL grabs the TOTAL of hours of that day
and multiplies it by either one or the other chart I have in Sheet2.

Any help apreciated!

Thanks,

Mm Via

 

[Frank Kabel]

Hi
see below

Hello

I've been trying to find out help without posting, reading other posts,
but didn't...

So here goes my problem:
(I only discovered EXCEL last week...!)

I want to create a workbook that calculates my salary automaticaly just
by entering the day of work and the hour I started and the hour I got
out (I get payed by the day).

1. So, first I need to calculate the TOTAL of hours of work in a given
day knowng the hour I started and the hour I finished.

EX:
A B
08:00 20:00 - Starting hour
22:00 07:00 - Ending hour
14 11 - TOTAL in HOURS (without minutes)

2. Then, here's the tricky part... My salary changes if it is a week
day, or a weekend day (and holiday).
So, I need EXCEL to know the weekday of the date I insert (ex: I insert
08.03.2004 and EXCEL reconizes it as MONDAY), and from there multiply
the number of HOURS of that day in one of two charts I have in another
sheet; one for MON, TUE, WED, THU, FRI, and the other for SAT, SUN and
HOLIDAY...

What I did so far:

I have a 1st sheet like this:

A B
1 DAY Day1
2 DATE 08.03.2004
3 WEEKDAY monday
4 FROM 08:00
5 TO 22:00
6 TOTAL 14
I want to enter B2 and EXCEL imediately fills B3 with the week day.
I want to enter B4 and B5 and EXCEL automaticaly discovers B6 - the
TOTAL of hours (even if it goes over midnight).

B3:
=B2
and format this cell with the custom format "DDDD"

B6:
=B5-B4+(B5<B4)
and format this cell as time

Then, already knowing if I worked in a weekday or a weekend day (are
holidays even possible?), EXCEL grabs the TOTAL of hours of that day,
and multiplies it by either one or the other chart I have in Sheet2.

Not quite sure what you mean with chart but try
For weekend/workdays:
=IF(WEEKDAY(B2,2)>5,B6*weekend_rate,B6*weekdayrate)

If you also want to include holidays lets say you have a list of all
holiday dates in D150 try
=IF(COUNTIF(D150,B2)>0,B6*holiday_rate,IF(WEEKDAY(B2,2)>5,B6*weekend_
rate,B6*weekdayrate))



Frank

 

[viangogh]

Hi!

B3:
=B2
and format this cell with the custom format "DDDD"

I tried this, but the result shows up as DDDD, not as a weekday. I als
tried:

B3 =WEEKDAY(B2)
and formating the cell as "DDDD" ou "dddd", and it didn't work...

The hours worked fine. Thanks!

What I meant with "chart" was a table with the value of let's say 1
hours of work on a weekday, 10 hours of work on a weekend day, and th
price of each extra hour both in week schedual and weekend schedual...

EX: (Sheet2 - TABLE)

______A___________B__________C_____
1 Weekday | 10h. | $200
2 Weekday | 1h.+ | $25
3 Weekend | 10h. | $250
4 Weekend | 1h.+ | $35


So if I work 14h. on a monday (displayed on the other sheet in B6) m
total would be =IF(B6>10, $200+((B6-10)*$25), $200) or something lik
that.
But if I worked those 14 hours on a Saturday, it would have to use th
other values to calculate ($250 and $35)...

Thanks again! I can see the woorkbook coming to life

 

[Frank Kabel]

Hi
enter this formath without the apostrophes. Just simply DDDD.
for the second one (overtime, etc.9 have a look at
http://www.cpearson.com/excel/overtime.htm
and
http://j-walk.com/ss/excel/files/timesht.htm

 

[viangogh]

Hello!

Thanks for the help!
Almost everything is working as I wished now...!

I have one more doubt, maybe you can help me there too...
I have posted it under "Leave cell blank", here goes a copy of tha
post:

«Hi

I have a workbook that calculates my salary on a daily and hour basis
In the first sheet I enter the date, the start and end hour, and i
calculates everything. I have room for 9 days per workbook.

In a second sheet, where I have a table that I can print out and sen
to my "boss", I have one row per day I worked. Excel automatical
enters the date and hours that I entered in the first sheet in th
correspondent row.

THE PROBLEM:
If I don't work 9 days (imagine I work only 2), I leave the other
cells in sheet 1 with the descripton, like "DATE", instead of a rea
date. I want the other 7 columns in sheet 2 (of the days I didn't work
to be left BLANK, instead of automaticaly showing up "DATE" (since i
is what I have written in sheet1...)

In sheet 2 I have:

_|___A__|____B____|___C____|___D____|
1|__Day_|___Date__|_Hours___|_Salary__|
2|___1__|_03-04-04_|___14___|__$140__|
3|___2__|_04-04-04_|___12___|__$120__|
4|___3__|__DATE___|____0___|___$0___|
5|___4__|__DATE___|____0___|___$0___|

B2=Sheet!1B2
B3=Sheet!1B3
B4=Sheet!1B4,
etc

And I what I want to have is:

_|___A__|____B____|___C____|___D____|
1|__Day_|___Date__|_Hours___|_Salary__|
2|___1__|_03-04-04_|___14___|__$140__|
3|___2__|_04-04-04_|___12___|__$120__|
4|______|_________|________|________|
5|______|_________|________|________|


Anyone can help?

Thanks!

 

[Frank Kabel]

Hi
see your reply for your new post