Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help wtih date and time in the same cell
I can get it to give me the time but it will come up 0's in the date. As shown A1 B1 C1 01/03/05 02:00 PM 01/03/05 02:59 PM 01/00/00 00:59 What I want it to do is show the same date ( i.e. 01/03/05) and minutes using this function.(=TEXT(MOD(B15-A15,12),"hh"" hrs ""mm"" mins""") in the same cell. Also, if it runs into the next day I want to put time restrictions on it as well. (i.e. the work time ends 7pm and resumes at 8am) I have figured out how to get the times Using this function too (b1-a1=c1) gives the minutes, but I want to show the date as above shown B1, but I keep coming up with what is shown in C1 above. can any one help? |
#2
|
|||
|
|||
How is Excel supposed to know what date you are talking about? You have to
enter the date somewhere. If the date is in A1 and a time in B1, =A1+B1 and format as mm/dd/yy hh:mm A/P should work. On Tue, 8 Feb 2005 12:01:05 -0800, "Michaela" wrote: I can get it to give me the time but it will come up 0's in the date. As shown A1 B1 C1 01/03/05 02:00 PM 01/03/05 02:59 PM 01/00/00 00:59 What I want it to do is show the same date ( i.e. 01/03/05) and minutes using this function.(=TEXT(MOD(B15-A15,12),"hh"" hrs ""mm"" mins""") in the same cell. Also, if it runs into the next day I want to put time restrictions on it as well. (i.e. the work time ends 7pm and resumes at 8am) I have figured out how to get the times Using this function too (b1-a1=c1) gives the minutes, but I want to show the date as above shown B1, but I keep coming up with what is shown in C1 above. can any one help? |
#3
|
|||
|
|||
I have time and date (i.e 01/03/05 03:30 pm) in one cell A1 and Time and
date (i.e 01/03/05 03:35 pm)in another cell B1. I am subtracting B1-A1. I want to show the date and time in c1.( i.e 01/03/05 00h 05mins) I have figured out how to show the time, but the date is the problem When I just subtract B1-A1 I get 01/00/00 00h 05mins. Do I need to run macro's in order to get this? "Myrna Larson" wrote: How is Excel supposed to know what date you are talking about? You have to enter the date somewhere. If the date is in A1 and a time in B1, =A1+B1 and format as mm/dd/yy hh:mm A/P should work. On Tue, 8 Feb 2005 12:01:05 -0800, "Michaela" wrote: I can get it to give me the time but it will come up 0's in the date. As shown A1 B1 C1 01/03/05 02:00 PM 01/03/05 02:59 PM 01/00/00 00:59 What I want it to do is show the same date ( i.e. 01/03/05) and minutes using this function.(=TEXT(MOD(B15-A15,12),"hh"" hrs ""mm"" mins""") in the same cell. Also, if it runs into the next day I want to put time restrictions on it as well. (i.e. the work time ends 7pm and resumes at 8am) I have figured out how to get the times Using this function too (b1-a1=c1) gives the minutes, but I want to show the date as above shown B1, but I keep coming up with what is shown in C1 above. can any one help? |
#4
|
|||
|
|||
Assuming both dates are the same, you can use the formula
=INT(A1)+(B1-A1) and format appropriately. But what do you want if the two entries are on different days? Is that possible? On Wed, 9 Feb 2005 11:39:09 -0800, "Michaela" wrote: I have time and date (i.e 01/03/05 03:30 pm) in one cell A1 and Time and date (i.e 01/03/05 03:35 pm)in another cell B1. I am subtracting B1-A1. I want to show the date and time in c1.( i.e 01/03/05 00h 05mins) I have figured out how to show the time, but the date is the problem When I just subtract B1-A1 I get 01/00/00 00h 05mins. Do I need to run macro's in order to get this? "Myrna Larson" wrote: How is Excel supposed to know what date you are talking about? You have to enter the date somewhere. If the date is in A1 and a time in B1, =A1+B1 and format as mm/dd/yy hh:mm A/P should work. On Tue, 8 Feb 2005 12:01:05 -0800, "Michaela" wrote: I can get it to give me the time but it will come up 0's in the date. As shown A1 B1 C1 01/03/05 02:00 PM 01/03/05 02:59 PM 01/00/00 00:59 What I want it to do is show the same date ( i.e. 01/03/05) and minutes using this function.(=TEXT(MOD(B15-A15,12),"hh"" hrs ""mm"" mins""") in the same cell. Also, if it runs into the next day I want to put time restrictions on it as well. (i.e. the work time ends 7pm and resumes at 8am) I have figured out how to get the times Using this function too (b1-a1=c1) gives the minutes, but I want to show the date as above shown B1, but I keep coming up with what is shown in C1 above. can any one help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining date and time into one cell | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |