need help with DATEIF formula

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Hello forum friends, I have an issue with the results of a DATEIF formula I am trying to build. I am receiving a result of $161.92 and it should be $50.06, so I am WAY off somewhere. I have included snapshots of the cells in question below. If anyone has any ideas, I will be grateful.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0 " /><col /></colgroup><thead><tr style=" background-color: #E0E0F0 ;text-align: center;color: #201116"><th></th><th>E</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;font-style: italic;;"> $ 7,910.23 </td></tr><tr ><td style="color: #201116;text-align: center;">5</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;font-style: italic;;"> $ 161.92 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0 ;color: #201116">totals</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF " ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF ;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0 ;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0 ;color: #201116">E5</th><td style="text-align:left">=SUM(<font color="rgb(255">E4/DATEDIF(<font color=" 0">'2018'!C2,'2018'!D2,"d"</font>)+DATEDIF(<font color=" 0">'2019'!C2,'2019'!D2,"d"</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0 " /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0 ;text-align: center;color: #201116"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;font-style: italic;;">01-Jan</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;font-style: italic;;">29-Mar</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0 ;color: #201116">2018</p><br /><br /><b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0 " /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0 ;text-align: center;color: #201116"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;font-style: italic;;">05-Feb</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;font-style: italic;;">17-Apr</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0 ;color: #201116">2019</p><br /><br />
 

MimiM

New Member
Joined
Apr 23, 2018
Messages
24
Bracketing. If you want to divide what's in E4 by the sum of the two date differences, then the formula is

E4/(datedif(...) + datedif(...))

What your current formula is doing is dividing what's in E4 by the first DATEDIF result and then adding the second DATEDIF result
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Thank you so much! Works perfectly now.

Cheers!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
@leopardhawk
What are you using to post you sample data? It is not displaying very well!
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Hi Peter, thank you so much for reaching out about this. It's called "Forum Tools" and I found the link to it on this forum. What's strange is that when I 'preview' the post, it looks perfect and then as soon as I submit the post, the results are not so good. It has worked a few times but I have no idea why it isn't consistent. I us the tool the same way every time. Also, I did reach out a while back to ask for any ideas and no one responded. If you know why this might be happening, I would love to hear back from you.

Cheers!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
It's called "Forum Tools" and I found the link to it on this forum. .. If you know why this might be happening, I would love to hear back from you.
You may not be using the latest version. Follow the 'Look here' link in my signature block below & you will there find a link to the latest version. Make sure you read the N.B. points at the end of post 2 in that thread.
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Hi Peter, thank you so much for that. I'm at work now but as soon as I get home, I will certainly follow up and try to make sure that I have the latest version. Thanks again. Have a great day!
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Hi Peter, okay, I tried deleting the old file for Forum Tools and downloaded a new one (by the way, I have only been using the previous one since May of this year when I first downloaded it). Anyway, you can see in the 'Test' section that the results are the same. BTW, I am using Windows 10, Chrome browser, Excel 2016... Any ideas??

Cheers!
 

Forum statistics

Threads
1,082,138
Messages
5,363,351
Members
400,729
Latest member
Lisa McConachy

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top