I'm trying to adjust a logbook and need to add 30min to a value 10-Sep-2012 07:28AM. Is there a formula to add an amount of time to this value?
2 Answers
If you don't want to do the calculation with 1440, just use
=A1+"0:30:00"
or
=A1+TIME(0,30,0)
=A1 + 30/1440
where 1440 is the number of minutes in a day (24×60). Alternatively, you could say
=A1 + 30/(24*60)
answered Jun 29, 2013 at 2:31
-
I've tried all 4 (2 from Teylyn and 2 from Scott) variations of answer with no luck. It may be because the cell contains the date and time. This cell is formated as "General". Do I need to change it "text" or "time" or "date"? Jun 30, 2013 at 23:36
-
@Jims: If it’s displaying a number, then it’s giving you the correct answer –– displayed as the number of days since January 1, 1900. Of course that’s not human-usable. Yes, you should format it as a date/time. That’s a little tricky; the easiest way is (1) click on a cell that’s already formatted the way you want (e.g.,
A1), (2) click on the “Format Painter” button (what it looks like) in the “Clipboard” pane on the “Home” tab, and then (3) click on the cell that contains the=A1+…formula. That copies the format from the first cell to the second. Jul 4, 2013 at 2:03 -
Alternatively, you can do an ordinary “Copy”, and then “Paste Special…” –> “Formats”. Jul 4, 2013 at 2:04
-
@Jims: By the way, welcome to Super User. For your information, when you respond to a post or a comment (in a new comment), it’s conventional to mention the author’s name, preceded by “@”, as in “@Scott”. That way he gets notified. You can abbreviate, or use an entire name (without spaces) as in “@JimsLogbook”. The author of a post is automatically notified of comments to that post (although, for reason(s) unknown, I didn’t see your above comment until today). See the Replying in comments paragraphs of this help page. Jul 4, 2013 at 2:09
-
If my suggestion does not work, that is most probably due to the date not being a real date. If the cell format is "General" you should see a number, not a date. If the cell shows something that looks like a date and does not react to any formatting, it is just text. In that case you will need to change your text "dates" to real date values. Why don't you post a sample file with a few of your values, so we can have a look? Post to a file sharing site of your choice and share the link.– teylynJul 4, 2013 at 10:19
