Function Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a date: 01/23/04 0:00 which is the midnight hour beggining that date. The formula looks like so: =DATE(YEAR(NOW()),1,1) I would like to make the actual date be 01/23/04 7:00 AM and 01/23/04 6:59:59 AM. Since it only adding a fraction of time in hours and minutes, how can I add the hours I need? The time needed is 420 or 419 in minutes, do I use a format function as part of the equation it to add it to my time value

Any assistance will be appreciated

Thanks in advance.
 
Here are 3 ways to add 7 hours:

=DATE(YEAR(NOW()),1,1)+TIME(7,,)
=DATE(YEAR(NOW()),1,1)+TIME(,420,)
=DATE(YEAR(NOW()),1,1)+"07:00"

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a date: 01/23/04 0:00 which is the midnight hour
Click to expand...
beggining that date. The formula looks like so: =DATE(YEAR
(NOW()),1,1) I would like to make the actual date be
01/23/04 7:00 AM and 01/23/04 6:59:59 AM. Since it only
adding a fraction of time in hours and minutes, how can I
add the hours I need? The time needed is 420 or 419 in
minutes, do I use a format function as part of the
equation it to add it to my time value?
 
Times in XL are stored as fractional days, so you just need to divide
hours by 24 (or minutes by 24*60=1440):

A1: 1/23/2004 0:00
A2: =A1 + 7/24 ==> 1/23/2004 07:00
A3: =A2 - 1/1440 ==> 1/23/2004 06:49

You may need to explicitly format A2 and A3.
 
Hi Ralph!

The following should help.

I have formatted A1:F1 dd-mmm-yyyy hh:mm:ss

I have:
A1:
=TODAY()
Returns: 24-Jan-2004 12:00:00
B1:
=A1+TIME(7,0,0)
Returns: 24-Jan-2004 7:00:00
C1:
=A1+TIME(6,59,59)
Returns: 24-Jan-2004 6:59:59
D1:
=A1+TIME(0,420,0)
Returns: 24-Jan-2004 7:00:00
E1:
=A1+TIME(0,419,0)
Returns: 24-Jan-2004 6:59:00
F1:
=A1+420/(24*60)
Returns: 24-Jan-2004 7:00:00

F1 is based upon time being recorded as a decimal of 1 day. So to add
minutes divide by 24*60

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top