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

[MurraySMTH]

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.

 

[Rick Rothstein]

I don't see how unless the cells are blank.

 

[MARK858]

I am adding this formula to column J.

@MurraySMTH You cannot input a date into a cell and then have a formula in the same cell.

 

[MurraySMTH]

What about using an IF statement to just read the "AM" in column L.

So if L3 = AM add one day to column J.

 

[Joe4]

MurrarySMTH,

You need to take into account what Mark said. A cell in Excel can either have:
1. A hard-coded value
- or -
2. A formula
but it can NEVER have both simultaneously!

So, you either need another cell, or you need to use VBA.

 

[MurraySMTH]

MurrarySMTH,

You need to take into account what Mark said. A cell in Excel can either have:
1. A hard-coded value
- or -
2. A formula
but it can NEVER have both simultaneously!

So, you either need another cell, or you need to use VBA.

Got it. Any tips for this to be a VBA?

 

[Joe4]

You need to tell us exactly how it should work. A lot of it depends on how data is entered into the worksheet.

There are basically two different kinds of VBA code that could be applied here.
1. "Event Procedure" VBA code is VBA code that runs automatically upon some "event" happening. In most cases like this, that would be the manual update of a certain cell, column, or row. So if someone where entering those times into those columns, you could have the code automatically run whenever someone manually updates the value in column L.
2. The other kind is just VBA code that you run manually when you want (what people often call "Macros"). So let's say that you are opening a file that has all the data in it, and you want it to run against all the data at once. Then you just select the Macro and run it whenever you like.

 

[Rick Rothstein]

I am wondering if the answer isn't to simply add another column for ending date (that could be a formula then)? Otherwise, how will you keep track if the correction was applied or not?

 

[MurraySMTH]

Rick, Yes I think that might be the answer.

Joe,

I am running a report and copy/paste data... the report is designed to give timespans for shifts. If the timespan in column L exceeds 12:00am i would need it to ADD the date by one day to column J.

This would follow a macro.

 

[Joe4]

Rick, Yes I think that might be the answer.

Yes, that is what I was referring to when I said you either need another cell or VBA.

If that works for you, I would go with that approach, as it is simpler. You simply put the formula Rick gave you in some blank, unused column on your sheet.
You can call it whatever you like, "Adjusted End Date", or something like that.

 

[MurraySMTH]

Thank you all! You have been so attentive. Have a wonderful day.