Subtract 12 hours from date/time
in [General]
|
Prev: Sorting and COUNTIF
Next: Import XML
From: Cindy on 6 Apr 2010 22:08 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: ozgrid.com on 6 Apr 2010 22:18 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: FSt1 on 6 Apr 2010 22:26 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: Pritesh on 7 Apr 2010 01:38 Hi, See if it works this way; =IF(B1>A1,A1-time(8,0,0)) 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 "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: Pritesh on 7 Apr 2010 01:55
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 > > |