A little too over my head. Help, please?

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
I've got a formula for figuring vacation days as shown below. It works beautifully for me.
In COLUMN A I enter a code that represents a personal day, Sick Day, etc.

In COLUMNS B AND C are the start and end dates. The Third COLUMN is the total days.

Here's the formula ...
=IF(OR(ISBLANK(B10),ISBLANK(C10)),"",C10-B10+1)

Now, I need to allow for half days as vacation ... and it's messing me up.

COL A COL B COL C COL D
HD 10/10/06 10/10/06 .5

I need help adjusting my formula so that if it sees the "HD" code in Column A, it will apply a calculation for a half day.

I'm sorry if this is vague ... I've been messing with it for two days and just can't seem to get something that will work.

Regards,
Kevin!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Kevin

Try this:
=IF(OR(ISBLANK(B10), ISBLANK(C10)), "", IF(A10="HD", 0.5, C10-B10+1))

This assumes only 1 half day is taken at a time. If however the employee takes 2 consecutive half days and you are planning on entering it like this:

HD, 10/10/06, 10/11/06

The use this formula instead:

=IF(OR(ISBLANK(B10), ISBLANK(C10)), "", IF(A10="HD", 0.5*(C10-B10+1), C10-B10+1))

HTH, Andrew
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello Kevin and Andrew,

When there is a way there always more ways,

Sorry I prepare my answer before to see yours, so I am posting it as my approach is not the same:
Book2
ABCD
1DayOffStartFinishDays
2HD110/10/200611/10/20062.00
3HD28/10/20069/10/20061.00
Sheet1


The type of holiday or sick days should be entered as :
HD1 for full day
HD2 for half day.

Check if the result corresponds to expectations.
Have a nice end of a weekend
 

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
Way to go Andrew!

:biggrin:

Andrew ... thanks! It works very well. (With one minor exception)
... There's always one of those, isn't there?

At first, your formula would cause the total to equal a whole number.
For example:

COL A COL B COL C COL D
HD 10/10/06 10/10/06 1

This was because I had decimals off for this column.

If I enable decimals for the entire column, it will make it more difficult to read.
Where a person may have taken (5) days vacation, it will read (5.0) days, etc.

Is there a way that the "HD" (for Half Day) can trigger a change to force decimals in COLUMN D? And for any other entry in COLUMN A ... the total in COLUMN D would be non-decimal?

Argh. Sorry to be a pain. But I am very grateful for your expertise!
Kevin. :LOL:
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello guys,

To display decimal when there is and not when there is not just format your column as general not decimal.

After second thoughts, I realise my answer could have been interpreted wrongly.
By HD, I meant Holiday not Half Day meaning HD1 was for full days holidays and HD2 for half days holiday. Same approach can be used for sick days and is easy to subtotal as you only need to filter on the first 2 letters to get the total.
Formula:=(C2-B2+1)/RIGHT(A2,1)
Book1
ABCD
1DayOffTypeStartEndDayOff
2SD18/09/200610/09/20063
3SD211/09/200611/09/20060.5
4HD210/10/200612/10/20061.5
5
65
7Legend
8HD1HOLIDAYFULLDAY
9HD2HOLIDAYHALFDAY
10SD1SICKDAYFULLDAY
11SD2SICKDAYHALFDAY
Sheet1


Not meaning to interfere with Andrew good answer, I just thought I needed to clarify my answer. My apologies not to be clear in the first instance.
 

Forum statistics

Threads
1,136,584
Messages
5,676,659
Members
419,638
Latest member
GlenMc52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top