IF statement adding a day to a date from 12:00am - Help!

MurraySMTH

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am trying to solve a simple IF statement by adding one day to a date from a cell containing time.

=IF(L3>Time(12,00,0)Datevalue(J3+1))

So if L3 is higher that 12:00am add one day to the date in J3

If this is not the case, no change to date.

Thank you for any effort provided! Love this forum.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
So if L3 is higher that 12:00am add one day to the date in J3

If this is not the case, no change to date.
One way:
=IF((L3-INT(L3))>0,INT(J3)+1,INT(J3))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,672
Office Version
  1. 2010
Platform
  1. Windows
If cell J3 contains a real date (not a text one), then this should work...

=INT(J3)+(L3>0.5)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Rick and gaz_chops,

Isn't 12:00 AM midnight and not noon (12:00 PM is noon, which is half a day)?
Since their formula wasn't working correctly, I went by their description and not their formula.
 

MurraySMTH

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
These are the formats i am using.

1595263293484.png
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

Oops i misread it! Thinking of 12 hours!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,672
Office Version
  1. 2010
Platform
  1. Windows
Rick and gaz_chops,

Isn't 12:00 AM midnight and not noon (12:00 PM is noon, which is half a day)?
Since their formula wasn't working correctly, I went by their description and not their formula.
The only reason I assumed the OP meant noon is because all times are greater than midnight (except midnight itself I guess), so you would always be adding 1. Since that would make the question's wording strange (why wouldn't the OP have just asked how to add one day), I assume the OP meant midday.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
MurraySMTH,

What column are you putting this formula in?

Even though K and L only show times doesn't necessarily mean they don't have a date component (actually, all valid time entries do, it is just that the date part is hidden by using a format that only shows time). So how are K and L being populated? Manually? By a formula (if so, please post the formulas)?
 

MurraySMTH

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
Joe,

I will be adding 1 day to column J that will determine the time from column L
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,745
Members
413,929
Latest member
Hypatia

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
Top