Formulaic way to add minutes to an existing date time cell

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 4, 2015

• #2

Try

=A1+B1/1440

 

Upvote 0

J

jangell

New Member

Joined

Apr 12, 2010

Messages

38

• May 4, 2015

• #3

AWESOME...

Now if I wanted to add another column that took the value that resulted from the calculation and subtract that from the current time (sort of a countdown to when the task is due) can you assist me with that formula?

I have the current date time (=now()) in cell A1 if that helps.

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 4, 2015

• #4

OK, so
A1: =NOW()
B1: is the cell containing some Date/Time value
C1: is the cell containing the number of minutes (120) you want added to the date/time in B1

D1: =B1+C1/1440
E1: =A1-D1

 

Upvote 0

J

jangell

New Member

Joined

Apr 12, 2010

Messages

38

• May 4, 2015

• #5

I tried that and the formula returns a long decimal number, not a date/time

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 4, 2015

• #6

Format the cell with the formula as a Date/Time.

Dates and Times are just numbers.
Cell formatting makes them look like dates and times.

The DATE is the whole number (left of the decimal point).
Incrimenting by 1 since Jan 1 1900
So Today 5/4/2015 is the 42128th day since Jan 1 1900.

TIME is the decimal value (to the right of the decimal point)
Basically a fraction of a day.
1 hour is 1/24

6 hours = 0.25
12 hours is 0.5
etc..

 

Last edited: May 4, 2015

Upvote 0

J

jangell

New Member

Joined

Apr 12, 2010

Messages

38

• May 4, 2015

• #7

is there anyway to syetematically display this as a date and time?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 4, 2015

• #8

Yes, by formatting the cell as the Date and Time format you desire.
Highlight the cell with the formula
Right click - Format Cells
Click Date
Select an appropriate format.

Or choose Custom and manually enter the format, something like
mm/dd/yyyy hh:mm:ss

 

Upvote 0

J

jangell

New Member

Joined

Apr 12, 2010

Messages

38

• May 4, 2015

• #9

Sorry...Should have been more specific with the problem.

The issue that I encounter is that when the calculation results in a negative the formula displays hashtags ######

In this case a negative means that I have missed the deadline where a positive means that the task is due in the future.

http://i1371.photobucket.com/albums...Shot 2015-05-04 at 2.11.21 PM_zpsbqq9vil8.png

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 4, 2015

• #10

Excel doesn't really understand Negative Time Values (humans can't travel backwards through time...yet).
So formatting a negative value as a time just doesn't make sense, so it displays ####

What would you like to happen instead of the #### when the value is negative?

Maybe you could do

E1: =IF(A1-D1<0,"Overdue",A1-D1)

Still formatted as a date/time

 

Upvote 0