#### Special-K

##### Board Regular
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Andrew Fergus

##### MrExcel MVP
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

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

HTH, Andrew

#### sunnyland

##### Well-known Member
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
Way to go Andrew!

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.

#### sunnyland

##### Well-known Member
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.

Replies
9
Views
527
Replies
5
Views
302
Replies
2
Views
800
Replies
4
Views
195
Replies
3
Views
659

1,171,060
Messages
5,873,533
Members
432,982
Latest member
meepio

### 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.

### Which adblocker are you using?

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

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