# help needed with excell formula

Discussion in 'Business Applications' started by rama4672, Oct 6, 2003.

Not open for further replies.

Joined:
Jun 1, 2003
Messages:
84
What i want to do is take a start date and then get it to count the number of days between the end date

IE: A1 would be 12/09/03 A2 would be the end date and A5 would hold the formula.
So if a chart was issued on 12/09/03 it would count the number of days and display in A5 until the chart was returned and the date of return was put in col A2 then A5 Should be blank.

Tia
Rama

2. ### kiwiguy

Joined:
Aug 17, 2003
Messages:
17,584
In A5 simply subtract A1 from A2.
Format A5 as a number, not as a date.

Excel handles date arithmetic fine, subtracting 1 date from another gives the number of days.

Just re-format the cell where the formula is, as it will take on a date format by default

I cant quite comprehend the fine detail you want following that.

3. ### kiwiguy

Joined:
Aug 17, 2003
Messages:
17,584
In A5:

=IF(A2="",INT((NOW()-A1)),"")

Will put the number of days between the date in A1 and NOW(), being todays date and time.

As soon as A2 has any entry in it, A5 goes blank.

Joined:
Jun 1, 2003
Messages:
84
thanks for the quick reply. but there is no date in A2 to take away, the charts are issued to a driver and the date put in col A1 then they have 21 days to return the charts back to us, so it must calculate how many days has passed since being issued with the chart.
When the chart is returned we then put the return date in A2 and then A5 should be blank so that we know that the chart has been returned

Tia

Rama

Joined:
Jun 1, 2003
Messages:
84

Thanks again

Rama

6. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
=IF(ISBLANK(A1),"",NOW()-A1)

It probably does the same as KiwiGuy, but might be easier to understand for us non-mathematicians.

Sorry, kiwiguy. I feel like I'm picking on you! I'm not! I swear!

7. ### kiwiguy

Joined:
Aug 17, 2003
Messages:
17,584
Your answer may give unwanted decimal places, as NOW() includes the time.

=IF(ISBLANK(A1),"",INT(NOW()-A1)) may work better. (E&OE)

You picking on me?
Never.
Kiwis may be endangered species, but they have a sense of humo(u)r and a thick skin.

Cheers

8. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
ROFL!!

For the record: formatting the cell gets rid of unwanted decimals too, and he doesn't want to return a date/time, he needs a number of days.

More importantly, when I was in the Navy, I took a "jump test" so I could go flying from time to time with my bosses, who were pilots. We had to jump out of this dead airplane and onto a trampoline-kinda-thing. Really stupid. But it was called the Kiwi test. I got to go flying twice in a T34-C. WAY cool. And I did not get sick!

9. ### kiwiguy

Joined:
Aug 17, 2003
Messages:
17,584
Because Kiwis dont fly I guess?

We do that a lot down here, but with bungy cords attached.
No wimpy Trampolines!

Now, jumping out of a flying plane onto a trampoline, thats more likely to be an achievement.

You would make your mark that way.

Joined:
Jun 1, 2003
Messages:
84
thanks for the input guys... that works OK but when a date is added in to cell A2 i need the cell that holds the formula to be left blank

Tia

Rama

11. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
Dreamboat and the Flying Kiwi's:

What is the world's preocupation with NOW ? I hate using it unless I have to have the time. Why not DBAs formula:

=IF(ISBLANK(A1),"",TODAY()-A1)

and Rama....what you you mean by the last response?
The above formula should leave cell A3 blank until a date is added. What is it then that you want?

I was understand - but now are cornfused!

- Castleheart

As Seen On