Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(40% Discount Ends Soon!)

Date Difference Countdown (days To Go & Passed)

I have a spreadsheet counting down the days until a date is reached. However, after that date is reached and passed I get a #NUM! error.

What can I do to countdown the days as below AND countdown the days that have passed as a NEGATIVE NUMBER???


=DATEDIF(NOW(),D11,"D")


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(40% Discount Ends Soon!)

View Course




Similar Topics







Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Any advice is greatly appreciated!

Happy New Year!

~ Brenda ~


I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.


I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.


I was able to convert start date and and end date to the number of days
inbetween the two dates. Does anyone know how to then convert the number of
days into months?



HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)


Is there a way to calculate the average days between a column of dates?

Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?

Thank you in advance!
~cp


I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()-A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completed-on date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)


Very new in this board. If I post this question in the wrong forum please bear with me.
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions


I use Excel 2000
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power



Hello, please help me out with this one.

So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!


Hello,

I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:

01/01/2007
02/01/2007
03/01/2007

I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.

Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!

Many thanks,
Caitlin


I need help writing a formula to add a column of numbers until a value is reached, then start over adding numbers starting at the next cell after the value is reached.

Thanks for any help.

45Romeo


I have attached a schedule for shift allowance calculation. The conditions for payment are as follows:

3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)

If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)


The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.


From limited experience I know that excel calculates dates via serial numbers.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.


I have a workbook that has compliance dates in columns "F","G" and "H" from row 7. What I need is when the date in either column comes within 30 days to auto send an email, address in column "A", recipients name in column "B". and then place todays date in column 'P". ALso need to send a follow up email when either date comes within 7days and then place todays date in column "Q". If there is a date in column "P" then don't send email. If there is a date in column "Q" then don't send follow up. Can this be done without the users intervention and each time the workbook is opened.
Thanks in advance for any assistance.

Mick


A previous poster explained what I also need; there were no responses,
so I thought I would try again.

Does anyone know of an Excel template--or a small stand-alone
program--that will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or low-cost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.

Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."

Thanks very much.




This formula is supposed to calculate difference between today and date hired to give years and months of service. Works fine for all except if the person was hired in year 2000 then this formula shows 110 y. Any one know how to fix this? or have a different formula that works.

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks
Rick


Hi folks,

Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close.

Been trailing the net and this board but can't work out how to do a simple variance.

How do I work out the difference between two numbers - including negatives.

If the numbers are all positive, it's fine as it's simply a case of A - B = C which is your difference. However, that doesn't work if B is negative.

I need a formula that would give the following:

First Number / Second Number / Difference
1 / 5 / 4
1 / -5 / -6
-1 / 5 / 6
-1 / -5 / -4
-5 / -1 / 4

Hopefully there's a simple function I've been missing.

Thanks in advance, R


I have a spreadsheet set up with an employees information, including the start date. After each year that the employee is working, he gets bumped up on the pay scale. For this reason, I only need to know how many years the employee has been working, rounded down to the nearest year.

This is what I have so far (hire date is in column B):

=((TODAY()-B4)/365)&" YEARS"

This function gives me a number with many decimal places.

I tried:

=ROUNDDOWN((TODAY()-B4)/365),0)&" YEARS"

It tells me I have too few arguments. Please help!


I have a template that automatically populates the date field with the
current date when opened, using today(). Users then Save As to have an
archive copy of their spreadsheet. However, when they go back to access
their saved sheet, the date changes from the created date to the current
date. How do I stop this in Excel?

I know that Word lets you do this by changing the code from {DATE} to
{CREATEDATE}, but I can't find a similar setting in Excel.

Please help and thank you!