Solved excel formulas: dates, times, and referencing

September 25, 2014 at 12:21:11
Specs: Macintosh
How do I make a cell refer to tomorrow's date if a certain time is entered? (12am)

Something like this?

=IF(E2=12:00 AM, TODAY ()+ 1, E2) but this is incomplete


See More: excel formulas: dates, times, and referencing

Report •


#1
September 25, 2014 at 13:07:35
Try this:

First: Format cell E2 as TIME either AM/PM or 24 Hr
Second: Format cell F2 as DATE

Now in F2 enter the formula: =IF(E2=12,TODAY()+1,E2)

See how that works for you.

The trick is you have to FORMAT first, then enter your data.

Also, this is probably not going to work the way you want it to.
What is it your trying to accomplish?


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
September 25, 2014 at 14:04:35
So here is a screenshot to make it a bit easier to understand:

http://i.imgur.com/aYn7Pj9.png

I want to make it so that if 12 AM is ever entered into "Time Out", that it refers to tomorrow's date.


Report •

#3
September 25, 2014 at 15:29:37
✔ Best Answer
This is usually accomplished by testing the two times against each other,
so with your data like:

       A         B          C
1)    IN        OUT       Total
2) 12:00 PM   12:00 AM    12.00

In Cell C2 you enter the formula:

=IF(A1>B1,B1+1-A1,B1-A1)*24

Excel stores Time as a fractional part of a 24 hour day.
So if you format cells A2 & B2 as Numbers,
you will see that Excel actually sees 12:00 PM as 0.50
and 12:00 AM as 0.00

EDIT ADDED: Formula should read:

=IF(A2>B2,B2+1-A2,B2-A2)*24


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •
Related Solutions


Ask Question