# Thread: Excel Formula (Excel 2003)

1. ## Excel Formula (Excel 2003)

I want to create a formula that will give me the number of weeks, months, days, hours, minutes and seconds between two dates and times. eg. 1Mar05 (being today's date) and 4 July 05 (event date) and 3:00 pm (event time). Can anyone help?

2. ## Re: Excel Formula (Excel 2003)

Do you mean:
A) There are 4 months, 0 weeks, 3 days and 15 hours between 1 March 05 midnight and 4 July 05 3 PM
or
[img]/forums/images/smilies/cool.gif[/img] There are 4 months between 1 March 05 and 4 July 05, or 17 weeks, or 125 days, or 3000 hours etc.

3. ## Re: Excel Formula (Excel 2003)

Thanks for your prompt reply. I don't think I really explained it properly. What I want is a formula to count down to an event to be able to say how many months, weeks, days and hours to an event. I have already look at doing the same as sample ([img]/forums/images/smilies/cool.gif[/img]. I hope you can understand what I want?

4. ## Re: Excel Formula (Excel 2003)

See the attached workbook. The worksheet is protected, so that you can only enter the date/time of the event. If you want to have a look at the formulas, or modify the date and time formats used, select Tools | Protect | Unprotect Sheet.

5. ## Re: Excel Formula (Excel 2003)

If the date/time to count down to is in cell A1, does this formula give you what you want:

<pre>=TEXT(DATEDIF(NOW(),A1,"m"),"0")&" Months "&
TEXT(INT(DATE(YEAR(A1-NOW()),1,DAY(A1-NOW()))/7),"0")&" Weeks "
&TEXT(MOD((DATE(YEAR(A1-NOW()),1,DAY(A1-NOW()))),7),"d")&" Days "&
TEXT(MOD(A1-NOW(),1)*24,"0")&" Hours"
</pre>

That formula all goes on one line. See the attached file.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•