Function Help

[Ralph]

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.

 

[Jason Morin]

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

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?

 

[JE McGimpsey]

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.

 

[Norman Harker]

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.