Help with dates wanted

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven - 16 Oct 2007 19:16 GMT

Can anybody help me with a dilemma I have in excel. It all revolves around
calendar dates, dates from and dates to and dates in between.

What I am trying to do is construct a spreadsheet which will show me a date
in the future from a date I have entered.

For example:

I want to put a date in say cell A1 (Date of Commencement of work) and from
this date a set of rules will apply.

I want to enter a date in cell A2 (first day of sickness) and from this date
be able to use a chart to determine how much sick pay an employee is
eligible for.

The rules of sick pay entitlement are time based (the longer the service the
more time allowed off sick etc)

If anyone could point me in the right direction re formulas etc I would be
most grateful.

I think that the DATEDIFF function may come into play here?

Reply to this Message

Ron Rosenfeld - 16 Oct 2007 19:49 GMT

>Can anybody help me with a dilemma I have in excel. It all revolves around
>calendar dates, dates from and dates to and dates in between.
[quoted text clipped - 18 lines]
>
>I think that the DATEDIFF function may come into play here?

Excel stores dates as numbers, with 1 = 1/1/1900.

So first you compute the amount of sick pay to which the employee is entitled.
Then add that to the first day of sickness. You may want to use the WORKDAY
function to do this addition.
--ron

Reply to this Message

Tyro - 16 Oct 2007 20:11 GMT

We cannot help you with a formula. How do you calculate the date in the
future manually? We need to know your rules.

Tyro

> Can anybody help me with a dilemma I have in excel. It all revolves around
> calendar dates, dates from and dates to and dates in between.
[quoted text clipped - 18 lines]
>
> I think that the DATEDIFF function may come into play here?

Reply to this Message

Don Guillett - 16 Oct 2007 23:16 GMT

You might make a table and use VLOOKUP (look in the help index) to get the
values desired.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Can anybody help me with a dilemma I have in excel. It all revolves around
> calendar dates, dates from and dates to and dates in between.
[quoted text clipped - 18 lines]
>
> I think that the DATEDIFF function may come into play here?

Reply to this Message

Jambar - 18 Oct 2007 10:26 GMT

On Oct 16, 11:16 pm, "Steven" <skint4s...@skint4sure.adsl24.co.uk>
wrote:

> Can anybody help me with a dilemma I have in excel. It all revolves around
> calendar dates, dates from and dates to and dates in between.
[quoted text clipped - 18 lines]
>
> I think that the DATEDIFF function may come into play here?

Start Date     Sick Date from     Sick Date to     No of Days worked     Sick
days
12-Oct-07    12-Nov-07    14-Nov-07    31    3
15-Nov-07    25-Nov-07    25-Nov-07    10    1

Reply to this Message

Rate this thread: