Add or Subtract Time

[NJ786]

Hi


I was looking for some help on calculating time based on a column of time provided.


How values are displayed is shown below.


Original Time format...............Subtract 4 Hours...............Subtract 9 Hours...............Add 2 Hours...............Add 9 Hours

0:00.......................................20:00....................................15:00..............................2:00..............................9:00

0:30.......................................19:30....................................14:30..............................2:30..............................9:30

20:00

23:00

23:30

 

[NARAYANK991]

Hi ,


The first result ( Subtract 4 hours ) can be done by the following :


=IF(A2<"4:00",A2+"24:00"-"4:00",A2-"4:00")


You can follow this same pattern to get the others.


Narayan

 

[oldchippy]

Hi,


How about


=IF(A2<"24:00",(A2+1)-"4:00","") copy down

=IF(A2<"24:00",(A2+1)-"9:00","") copy down

=IF(A2<"24:00",(A2+1)+"2:00","") copy down

=IF(A2<"24:00",(A2+1)+"9:00","") copy down


I'm a little curious that you show that column B & C with 19:30 & 14:30, is that an error on your part or mine?

 

[NJ786]

thanks buddy

 

[NJ786]

Ooops my bad

 

[SirJB7]

Hi, NJ786!


I was wondering why not something like:

=A2-XXX/24

where XXX is the integer number of hours to be subtracted or added.

Simpler, isn't it?


Regards!

 

[NJ786]

24 hour clock issue


see what happens when i try to use the formula... =A1-4/24


where the day goes back to the previous date before 00:00 am, we get a negative value or too large value????


2:00 #######

3:00 #######

3:25 #######

20:15 16:15

22:45 18:45

23:00 19:00


NJ

 

[oldchippy]

Hi,


Away round that one is


=A2+1-4/24

 

[NJ786]

wow! now i have so many options...


thanks every one, i thing that should do the job...


appreciated once more


NJ

 

[oldchippy]

Thanks for the feedback, glad we helped

 

[SirJB7]

Hi, NJ786!

oldchippy correction is absolutely right. I didn't notice that because I tried with recently hours in format "dd/mm/yyyy hh:mm:ss" with the NOW() function instead of just hour values, so I never went before 31/12/1899 (or 00/01/1900). Sorry.

Regards!


@oldchippy

Hi!

Thanks for the update.

Regards!