Subtract 12 hours from date/time
in [General]
|
Prev: Sorting and COUNTIF
Next: Import XML
From: Cindy on 7 Apr 2010 14:14 Hmm, you are right. Once I changed the time to 20:30 it worked. My old military mind doesn't see time with a colon in it. LOL Thanks! Cindy "ozgrid.com" wrote: > Valid date & times would like: 3/3/2010 20:30 else your date and times are > text. > > > > -- > Regards > Dave Hawley > www.ozgrid.com > "Cindy" <Cindy(a)discussions.microsoft.com> wrote in message > news:885F3AA7-8027-4726-9C0B-8C543332F132(a)microsoft.com... > >I am trying to subtract 12 hours from a date/time cell if the date in B1 is > > greater than A1. > > > > A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm) > > B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm) > > > > No matter how I format A1, B1 and C1 I still get a #Value error. I am > > obviously missing something or leaving something out. Any help is greatly > > appreciated. > > > > Cindy > > > > >
From: Cindy on 7 Apr 2010 14:17 Actually my formula was the same as yours, but I had the time in A1 and B1 formatted incorrectly. I was putting in 2030 (true military time) instead of 20:30 which is what Excel wants. Thanks for your help though! Cindy "FSt1" wrote: > hi > would have been nice if you had posted your formula so someone might see > what your are doing wrong. > > excel keeps time as a decimal value of a day. > 12 hrs = .5 day > > assuming that your date/time that you are subtracting is A.... > =IF(B1>A1,A1-0.5,0) > > post back with more details. maybe we can refine it more. > > regards > FSt1 > > > "Cindy" wrote: > > > I am trying to subtract 12 hours from a date/time cell if the date in B1 is > > greater than A1. > > > > A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm) > > B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm) > > > > No matter how I format A1, B1 and C1 I still get a #Value error. I am > > obviously missing something or leaving something out. Any help is greatly > > appreciated. > > > > Cindy > > > >
From: Cindy on 7 Apr 2010 14:18
This also worked but you hit the nail on the head with the erroneous data. I was putting in 2030 instead of 20:30. Thanks for the alternative formula, I've put it in my notebook for future knowledge! Cindy "Pritesh" wrote: > Hi, > > See if it works this way; (I assumed you want to reduce 8 hours from B1). > > =IF(B1>A1,B1-time(8,0,0),B1) > > If still you get error, your data might be errorneous. Then your first step > should be to fix data-error. Please post your feedback or progress. > > Regards, > Pritesh > > -- > Regards, > Pritesh > > > "Cindy" wrote: > > > I am trying to subtract 12 hours from a date/time cell if the date in B1 is > > greater than A1. > > > > A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm) > > B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm) > > > > No matter how I format A1, B1 and C1 I still get a #Value error. I am > > obviously missing something or leaving something out. Any help is greatly > > appreciated. > > > > Cindy > > > > |