Automatic Functions in Excel

[Colin Weir]

Hi There

I am developing a Time Sheet in Excel for Staff to record times worked
on it. I am using the 1904 date system as occasionally a negative
total will be displayed.

I would like it if someone was on annual leave they would be able to
record this by typing in "Annual Leave" or "A/L" or something similar
then the total for that day to display as 7:00 (this is in hours). At
present the "total" column runs a calculation of the time finished
minus the time started.

Is this possible?

Regards

Colin

 

[Per Jessen]

Hi Colin,

StartTime in column A, end time in column B, use this formula:

 

[Per Jessen]

I hit the send key by mistake, here's the formula:

=IF(A2="A/L","7:00",B2-A2)

Regards,
Per

 

[Colin Weir]

Hi Per

Thanks for your help - works great. Has caused another problem
however, the cell where I have put that formula into, the value in
there is then added together along with the other days of the week and
then totalled. However for some reason they are not adding up.

FYI - the value which is shown (7:00) is alligned to the left of the
box rather than the right like it was previously.

Thanks

Colin

 

[Pete_UK]

You need the formula to return a number, so do it like this:

=IF(A2="A/L",7/24,B2-A2)

Hours are stored in Excel as fractions of a day, hence the divide by
24 part.

Hope this helps.

Pete

 

[Colin Weir]

Hi

Working great now - thanks.

Is it possible to have or statements on the function or something
similar?

For example, I'm looking to have it that if the person is on Annual
Leave (as before) or public holiday.

I've tried =IF(A2="A/L""P/H,7/24,B2-A2) and =IF(A2="A/L"or"P/H,7/24,B2-
A2)

Neither work however. Is this possible?

Thanks again

Colin

 

[Colin Weir]

Apoligies - I have managed to do this now. Thanks for all your help.

Colin

 

[Pete_UK]

You're welcome, Colin - glad to hear that you got it working.

Pete