• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

DUE TO INSERTION OF TODAY(), FORMULA CONTINUING TO SHOW DIFFERENCE DELAY WHILE MATERIAL HAS ALREADY RECEIVED|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / DUE TO INSERTION OF TODAY(), FORMULA CONTINUING TO SHOW DIFFERENCE DELAY WHILE MATERIAL HAS ALREADY RECEIVED|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…DUE TO INSERTION OF TODAY(), FORMUL…
sp_PrintTopic sp_TopicIcon
DUE TO INSERTION OF TODAY(), FORMULA CONTINUING TO SHOW DIFFERENCE DELAY WHILE MATERIAL HAS ALREADY RECEIVED
Avatar
PRASHANT MANJREKAR

New Member
Members
Level 0
Forum Posts: 1
Member Since:
January 4, 2019
sp_UserOfflineSmall Offline
1
January 4, 2019 - 5:00 pm
sp_Permalink sp_Print

HI ALL,

Pls view attachement, copied in word. This is regarding excel formula. 'F3' is result of TODAY()-E3 (Date converted in numbers), so its showing due by number of dates by subtracting PO DUE DATE from TODAY(). But when G3 (Material received) is there, F3 still shows delay while there is no delay as material has already received.

If I am putting this formula in place of f3: "=IF(OR(G3>0, F3=0), TODAY()-E3, 0)", then its shows '0' which I required but same formula can not be use at places where material still NO RECEIVED and required to put DELAY in numbers 1,2 3,4,5,...etc. and not 0.

Please help

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
January 5, 2019 - 5:16 am
sp_Permalink sp_Print

Hello,

Welcome to MOTH.

It is always better to upload Excel files than Word documents if you have some issues with Excel formulas. But let's give it a try.

First some questions.
1) Do you still want to see number of days due if received date is after due date?
2) Are the cells in G column empty or do you have a zero (0) when no receipt has been made?
3) Are the dates in D, E and G columns in date format?

So, without having the answers on my questions I am doing some assumptions, 1) Yes, 2) 0, 3) Yes. If I am wrong in my assumptions then please correct my suggested formula. I am using a nested IF formula just to be able to explain the logic better. You find the finished formula at the end.

First control, does cell in G column have a zero (no delivery)?
=IF(G3=0,

If yes then let's check if PO DUE date is greater than todays date.
=IF(G3=0,IF(E3>TODAY(),

If yes then there is no late delivery, let's put in a zero.
=IF(G3=0,IF(E3>TODAY(),0,

But if no then there is a late delivery, lets show by how many days.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),

Now the second IF condition is closed, we are back to the first IF condition and to where G3 does not have a 0, but a date, so let's check if that date is greater than PO DUE date or not with a third IF condition.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,

If G3 is greater then we want to see by how many days the delivery was late.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,G3-E3,

And if the date in G3 is lesser than or equal to PO DUE date, then just put in a 0, the delivery was in time.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,G3-E3,0)

As we now have completed all our controls, let's close the formula with the last right parentheses.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,G3-E3,0))

I hope this solves your issue.

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
3
January 5, 2019 - 5:20 am
sp_Permalink sp_Print

Hi Mrashant Manjrekar, welcome to Mynda´s Forum.

I think when material has arrived it makes no sense to have a number in column F, right? Maybe a text as 'delivered' will do.

So I think in F3 the formula can be something like: =IF(G3<>0;"delivered",TODAY()-E3).

Or am I missing something?

Frans

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
4
January 5, 2019 - 5:22 am
sp_Permalink sp_Print

Sorry Anders, we were answering same time I think so I didn't saw your post until I posted mine.

Frans

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
January 5, 2019 - 5:56 pm
sp_Permalink sp_Print

Hello Frans,

Nothing to be sorry about, the more replies the better. It is always nice to see different solutions and approaches to a problem and yours is an example of keeping it simple, the first step to success. Wink

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Tristram Sexton
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.