Need some help here...Base on my sheet the M7 cell which the joined date have got a formula like this =VLOOKUP($C$3,VALIDATION!$A$2:$M$500,9,FALSE), so when I choose in C3 (drop-down list different employee number) the joined date in cell M7 will change, so from there the number of months in J11 will change according to the joined date. IF M7 GOT 1/3/2011 (IT NEED TO BE CHANGE AS 1/3/2013) SUBTRACT TO TODAY DATE 26/10/2014 SO I NEED THE ANSWER OF 19MONTHS (because 1/3/2013 to 1/3/2014 = 12 months or 1 year then + the 1/3/2014 to today date 26/10/2014 = 7 months so MUST BE TOTAL OF 12+7 = 19MONTHS... then only I can count how many entitled leave they can have to date. thank you again...
Hi, hope you can help me: I need my spreadsheet to highlight contractors whose contracts are about to finish in 3 months time from current date. How do I go about it please? thanks
Hi Doug, Do you know how I would set up an IF fx if I wanted to ultimately fill blank rows in a column that already has existing data in it. I would like to fill the blank spaces here with a date from a different column but only if another column has a "NO" in it. So for example: I have 5 columns total and column B has blank rows in it that I would like to fill but only based on column A. If column A has a "No", then I would like to put the date that is in column E to fill in this blank space in column B, but again, filling the blank space depends on if Column A has a Yes or No and will only use the date from column E....Is this possible? Thank you for any help that you can provide. Thanks again, Ann
Good morning sir. I am having some trouble with this basic function. I realize it is me however I can't seem to get this to work. I don't thing my version of 2011 excel matters however I am trying to take 31 Nov 2019 from 7 Sep 2014 to show the difference of how many day between. When I do the steps as you have My sheet comes up with #VALUE!, why is this?
The reason that DateDif (not dated If, Date Difference) isn't documented is because it always rounds down and even if you add 15 to days to the End date, it's still flakey if you want to calculate partial months. It's the same in SQL Server and honestly if you want the logical number of partial months between 2 dates then it really doesn't work.
I have been attempting to create a personal auto maintenance sheet that lists the specified intervals for particular maintenance items, to show specified mile intervals and month intervals. Going to the right, I list the odometer reading and date that maintenance item was performed. Showing remaining miles when to perform that same maintenance is easy, but I cannot figured out how to list the remaining months from when it was performed to when it will be needed again in months. I have the current date NOW at top form and a space to enter current Odometer reading to calculate the remaining miles. It's the month thing that I can't get. Months range from 12 to 84 months. Wish I could put my sheet up somewhere for someone to see.
Doug. great video as always.. never saw such an obscure function but very useful. It helped me to calculate the numbers of years to accrue an ARO liability. Thanks
hi dough, is it not applicable to 2007? i have tried but it said "the formula you typed contains an error" i have changed the format into date as well.
Hi Sir, It does not work with me in date format (30/12/2017) it works in single digit (3/12/2017) when I try to change date format it give me ( 3-Dec-17) but (30/12/2017) still remain as it is, and result was : #NUM!. can you solve it please
How could we calculate year/s and month/s in one row or column e.g. Job started on 06/2016 and ended on 08/2017 and the result should reflect as 1.2 years?
Hi Doug! I'm calculating the difference between 05/01/2015 and 07/31/2015. When I used the datedif function, it only gave me 2 months difference when it should've been 3 months. How do you go about this without necessarily adding 1 to the equation?
That was REALLY helpful and just what I needed to produce a report for work which saved me a lot of time and difficulty! Explained clearly and was very easy to follow. THANK YOU!!!
Hi Doug, I have small query i want to add exp of employees in YY::DD format. For Example if i have to find out total exp of an employee which includes previous exp and current experience. You can consider 2.10 (years n months)as previous exp and current comp exp is 2.11 (years n months) total should be 5.9 (years n months) but when try to sum i am not getting the same value i tried using year and month functions. Can you help me without using any macros.
Doug H thanks for the reply..yeah i know this way, but i always need to make adjustment by using this method. So i was wondering if there is any other way to calculate that.
I seldom works on my some worksheet but I was often confused to put out in dates figures, your tutorials give me a guide which is very useful. I got it very easily remember date formula. Thanks
I found a solution but it will only works with one variable at a time: One subject, location, start date/time, end date/time but not for the multiple variables that I have in my Excel table
Great video and definitely very useful.. I do have a question. I'm a military leader and have to produce yearly evaluations on specific personnel with different start dates. Example: If I have a Soldier that is promoted to Sgt, that Soldier is due an evaluation one year from the date of promotion. The number of Soldiers I have in this category is overwhelming and hard to track if I don't have a reminder. I would like to create a formula that will act as a countdown to the due date. I'd appreciate all the help I can get. Thanks
Hey Doug, Can you help me with a formula. I have a Date for example (12/19/2017) that I acquired a product in cell P2. I want to calculate the number of days it's been in inventory up until TODAY That's the first number I want that cell to show. I figured that's easy =Today()-P2 and yes that does display what I need. But when the product sells and I input the Sold date in Cell S2 i want it to show only the amount of days it took to sell the product rather than the number of days it's been in inventory. what should this formula look like? Thanks for your help!
Wouldn't it be the S2 (sold date) minus P2(acquired date)? It seems that if the product hasn't sold, the inventory date calculation should occur but if the item sold the days item was in inventory equals to the the days it took to sell the item.
Doug H yes your right. What I’m trying to do is display a running number of days in inventory up until the sold date is entered then I want that number to switch to the amount of days it took to sell it when the sold date is entered.
Try using a combination of ISBLANK function inside of an IF statement. It could be summarizes like if the sell date cell value is blank, then use the TODAY function minus the acquired date, if it's not blank then use the sales date minus the acquired date. Some vids on the above functions: ISBLANK => ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oZnI0RcGQZk.html IF => ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vNmZThJltOw.html
it might be that the function is looking for a number, but is doing calculation on text string. You may want to check out if the reference cells are strings or numbers.