|
Converting text format of time/date into Excel time/date for subtr
Hi,
I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY |
Converting text format of time/date into Excel time/date for subtr
=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIMEVALUE(MID(A1,FIND(" ",A1)+1,256))
-- Gary''s Student - gsnu201001 "YY san." wrote: Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY |
Converting text format of time/date into Excel time/date for subtr
YY,
You could try and just use =A3-A2 or, to be clearer in your formula, =VALUE(A3)-VALUE(A2) HTH, Bernie MS Excel MVP "YY san." wrote in message ... Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY |
time to words
Hi!
Please help me with the formula and how to converte numeric time into words. Example: employee knock in at 9:00 am and arrives at 8:45 am (and the answer is ontime) or if s/he came in 9:23 am ( answer is late) YY san. wrote: Converting text format of time/date into Excel time/date for subtr 02-Feb-10 Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Command Prompt Here with VS.NET Environment http://www.eggheadcafe.com/tutorials...here-with.aspx |
time to words
Anathi, Somethng like =IF(B2A2,"Late","On Time") Where B2 has the actual arrival time, and A2 has the scheduled arrival time. This will work if you enter times as AM and PM only, without dates. HTH, Bernie MS Excel MVP <Anathi Sonqishe wrote in message ... Hi! Please help me with the formula and how to converte numeric time into words. Example: employee knock in at 9:00 am and arrives at 8:45 am (and the answer is ontime) or if s/he came in 9:23 am ( answer is late) YY san. wrote: Converting text format of time/date into Excel time/date for subtr 02-Feb-10 Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Command Prompt Here with VS.NET Environment http://www.eggheadcafe.com/tutorials...here-with.aspx |
Thank you
Hi! Bernie
Im so pleased with help, I also tried to some other tricks =IF(B3=E3,"ontime", IF(B3<=E3,"late")) although if E3 is 0:00 and B3 is 0:00 instead of showing 0:00 it says ontime. Regards Anathi Bernie Deitrick wrote: Anathi,Somethng like=IF(B2A2,"Late","On Time")Where B2 has the actual arrival 04-Feb-10 Anathi, Somethng like =IF(B2A2,"Late","On Time") Where B2 has the actual arrival time, and A2 has the scheduled arrival time. This will work if you enter times as AM and PM only, without dates. HTH, Bernie MS Excel MVP <Anathi Sonqishe wrote in message Previous Posts In This Thread: On Tuesday, February 02, 2010 9:49 AM YY san. wrote: Converting text format of time/date into Excel time/date for subtr Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY On Tuesday, February 02, 2010 10:09 AM Gary''s Student wrote: =DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIMEVALUE(MID(A1,FIND(" =DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIMEVALUE(MID(A1,FIND(" ",A1)+1,256)) -- Gary''s Student - gsnu201001 "YY san." wrote: On Tuesday, February 02, 2010 11:08 AM Bernie Deitrick wrote: YY,You could try and just use=A3-A2or, to be clearer in your YY, You could try and just use =A3-A2 or, to be clearer in your formula, =VALUE(A3)-VALUE(A2) HTH, Bernie MS Excel MVP On Thursday, February 04, 2010 2:11 AM Anathi Sonqishe wrote: time to words Hi! Please help me with the formula and how to converte numeric time into words. Example: employee knock in at 9:00 am and arrives at 8:45 am (and the answer is ontime) or if s/he came in 9:23 am ( answer is late) On Thursday, February 04, 2010 8:21 AM Bernie Deitrick wrote: Anathi,Somethng like=IF(B2A2,"Late","On Time")Where B2 has the actual arrival Anathi, Somethng like =IF(B2A2,"Late","On Time") Where B2 has the actual arrival time, and A2 has the scheduled arrival time. This will work if you enter times as AM and PM only, without dates. HTH, Bernie MS Excel MVP <Anathi Sonqishe wrote in message Submitted via EggHeadCafe - Software Developer Portal of Choice EggHeadCafe Chat Chaos in Silverlight Released Today http://www.eggheadcafe.com/tutorials...-chaos-in.aspx |
Thank you
Well, that's what you've asked it to do, so naturally it will return
"ontime" in that situation. If you want it to return 0:00 in that situation, you need to put that in the formula. =IF(AND(B3=0,E3=0),0,IF(B3=E3,"ontime","late")) and format as time to suit. Note that if you've done a test for B3=E3 it is pointless to do another test for B3<=E3, because you wouldn't have got there unless that test was true. -- David Biddulph "Anathi Sonqishe" wrote in message ... Hi! Bernie Im so pleased with help, I also tried to some other tricks =IF(B3=E3,"ontime", IF(B3<=E3,"late")) although if E3 is 0:00 and B3 is 0:00 instead of showing 0:00 it says ontime. Regards Anathi Bernie Deitrick wrote: Anathi,Somethng like=IF(B2A2,"Late","On Time")Where B2 has the actual arrival 04-Feb-10 Anathi, Somethng like =IF(B2A2,"Late","On Time") Where B2 has the actual arrival time, and A2 has the scheduled arrival time. This will work if you enter times as AM and PM only, without dates. HTH, Bernie MS Excel MVP <Anathi Sonqishe wrote in message Previous Posts In This Thread: On Tuesday, February 02, 2010 9:49 AM YY san. wrote: Converting text format of time/date into Excel time/date for subtr Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY On Tuesday, February 02, 2010 10:09 AM Gary''s Student wrote: =DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIMEVALUE(MID(A1,FIND(" =DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIMEVALUE(MID(A1,FIND(" ",A1)+1,256)) -- Gary''s Student - gsnu201001 "YY san." wrote: On Tuesday, February 02, 2010 11:08 AM Bernie Deitrick wrote: YY,You could try and just use=A3-A2or, to be clearer in your YY, You could try and just use =A3-A2 or, to be clearer in your formula, =VALUE(A3)-VALUE(A2) HTH, Bernie MS Excel MVP On Thursday, February 04, 2010 2:11 AM Anathi Sonqishe wrote: time to words Hi! Please help me with the formula and how to converte numeric time into words. Example: employee knock in at 9:00 am and arrives at 8:45 am (and the answer is ontime) or if s/he came in 9:23 am ( answer is late) On Thursday, February 04, 2010 8:21 AM Bernie Deitrick wrote: Anathi,Somethng like=IF(B2A2,"Late","On Time")Where B2 has the actual arrival Anathi, Somethng like =IF(B2A2,"Late","On Time") Where B2 has the actual arrival time, and A2 has the scheduled arrival time. This will work if you enter times as AM and PM only, without dates. HTH, Bernie MS Excel MVP <Anathi Sonqishe wrote in message Submitted via EggHeadCafe - Software Developer Portal of Choice EggHeadCafe Chat Chaos in Silverlight Released Today http://www.eggheadcafe.com/tutorials...-chaos-in.aspx |
| All times are GMT +1. The time now is 08:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com