Calculating overdue by "Days" using dates

Excelme at work

New Member
Joined
Feb 7, 2018
Messages
18
Hi there, I have a schedule showing delivery information and would like to use a formula to show by how many days a delivery was either early or late.

I have previously used IF function to show if the delivered date cell is before or after the scheduled date cell.
But ideally i would like to show as either positive or negative number of days.
Is this possible?

Thanks in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Just subtract one date from the other for a date duifference (this assumes both dates ARE in fact dates and not text in any form).

A1 = scheduled date
B1 = delivery date

=B1-A1
If postive then its late, if negative then it's early
 
Last edited:
Upvote 0
Just subtract one date from the other for a date duifference (this assumes both dates ARE in fact dates and not text in any form).

A1 = scheduled date
B1 = delivery date

=B1-A1
If postive then its late, if negative then it's early


Thanks i had assumed that would work issue was with my cell formatting.
Taking it one step further would it be possible to calculate it as hours using Date and Time formt DD/MM/YY HH:MM
Scheduled - 12/01/2019 1300
Delivered - 13/01/2019 1700
Could i get an answer in decimal days or in hours with a separate function to /24 for decimal days.
Thanks
 
Upvote 0
Time in Excel is from 0 to 1. 1 is full day (24 hrs), so 0.5 would be equal to 12 noon.
If you use Custom format [h]:mm:ss it will allow for hours over 24.
Otherwise you'd need a formula to break in down into days hours minutes seconds.
 
Upvote 0
hey so with dates dd/mm/yyyy hh:mm:ss format so for example in column A you have scheduled date and in B you have Actual Delivery date to find out what is is
=(b2-a2)/0.04166
if its a minus its delivered early and if its positive its delivered late, and it will tell you how many hours its delievered late
 
Upvote 0
As Special-K99 mentioned, one day in a time field is by an increment of 1, so you could just format the difference as numeric with as many decimal places you want.

Here's your start and end dates with the difference displayed in [h]:mm:ss (28 hours)
[TABLE="class: grid, width: 249"]
<tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]=End - Start[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/19 13:00[/TD]
[TD="align: right"]1/13/19 17:00[/TD]
[TD]28:00:00[/TD]
[/TR]
</tbody>[/TABLE]
Here's the same data displayed as numeric with two decimals.
[TABLE="class: grid, width: 249"]
<tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]End - Start[/TD]
[/TR]
[TR]
[TD="align: right"]43477.54[/TD]
[TD="align: right"]43478.71[/TD]
[TD="align: right"]1.17[/TD]
[/TR]
</tbody>[/TABLE]
I would suggest leaving start/end formatted as date/time, but just wanted to show what the actual value of a date field is.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top