MS Excel Help with Formula

jemw

Hi Guys

Not sure if this is the right place to post but I thought I would ask on here rather than getting an Excel for Dummys book :money:

I have tried the help function on Excel but couldn't find the answer I was looking for... so the question is:

I want to insert a series of dates into a spreadsheet column and in the next column calculate the "age" in days (and also working days, if this is possible). It must be in days though...

So for example:

01 July 2009 = 32 days / 22 working days
25 July 2009 = 8 days / 5 working days
01 August 2009 = 1 day / 0 working days

Can anyone help me with the formula for this?

jemw

oh, and this needs to update itself each day??

Sagz_2

Sorry - don't understand

What do you mean by the 'age' of a date?

ManAtHome

As a quickie, number of days (age) is =ROUND((NOW()-A1-1),0) assuming the date is in cell A1. Number of days *5/7 would be a rough approximation of working days, but you'd really need to use a day of week function to be more exact.

jemw

Sorry, I wasn't sure if I had made myself clear! I want to be able to calculate how long something has been ongoing for..

Sso, for example, if I made a claim for something (like on an insurance policy, say for a repair of my boiler?) on 01 July 2009, I would like a formula to calculate how long (in days and working days if poss) the claim has been ongoing for?

PasturesNew

Draw up this spreadsheet:
Cell A1: "Date Now"
Cell B1: "Days in Month"
Cell C1: "Work Days"

Those are your headings

Now type dates in column A.

Looking at A2 here are the formulae for B2 and C2:

=DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)
=NETWORKDAYS(DATE(2009,7,1),A2)

That will give you row 2 working, now just drag those two down for the rest of the dates you have - EXCEPT - notice I keyed in the start date for the month of July in C2.... so that'd need changing. I should have given you the full formula that works it out based on column A I guess, but it's late.

jemw

Thanks so much for all of your replies! Shocked at how many considering it's nearly midnight on a Sunday!!

So, I've tried the =ROUND((NOW()-A1-1),0) and the /5*7 and that works a treat

Also tried the TODAY()-A1 and that works too..

Does anyone know more about the "day of the week function" that ManAtHome has posted about?

Many thanks again

jemw

Hi PaturesNew

Thanks for your reply..

I can't seem to get column C to work. I know you've said that you keyed in the start date for the month of july but I don't understand how to change it? So sorry to be a pain!!! Can you explain further please?

hundredk

Column A
=INT(NOW()) This will always be todays date when you open the file - format cell as a date

Column B
Your Date eg1/7/09 format cell as a date

Column C
=A-B format cell as a number

closed

=NETWORKDAYS(Startdatecell,endatecell,firstbankholcell:lastbankholcell)

jemw

some of the posts have disappeared???? i know this because i wrote replies to them and came back to thank ppl and they've gone??

blimey - it's too late on a sunday night and my eyes / brain is playing tricks on me!