# 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

#1
September 25, 2014 at 13:07:35
 Try this: First: Format cell E2 as TIME either AM/PM or 24 HrSecond: Format cell F2 as DATENow 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?MIKEmessage 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.pngI 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
 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)*24Excel 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.00EDIT ADDED: Formula should read:=IF(A2>B2,B2+1-A2,B2-A2)*24MIKEmessage edited by mmcconaghy