If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summarizing Date and Time
Hello, I am trying to take the date in the format of mm/dd/yyyy hh/mm/ss and
on another worksheet creat a summary which would have a date field, a seperate start time field, a seperate end time field, and a total time field. Any suggestions as to the formulas that might help me out? Thanks |
#2
|
|||
|
|||
Summarizing Date and Time
If you want to create a date out of the date/time data you can use
this formula: =DATE(YEAR(D7),MONTH(D7),DAY(D7)) and the time would be: =TIME(HOUR(D7),MINUTE(D7),SECOND(D7)) When you have a second time value you can just use regular math (since time is really just a decimal value in disguise) and add and subtract as you need. On Jan 29, 11:18 am, Sean wrote: Hello, I am trying to take the date in the format of mm/dd/yyyy hh/mm/ss and on another worksheet creat a summary which would have a date field, a seperate start time field, a seperate end time field, and a total time field. Any suggestions as to the formulas that might help me out? Thanks |
#3
|
|||
|
|||
Summarizing Date and Time
=INT(A2) gives the date.
=MOD(A2,1) gives the time. Format the cells appropriately. If you are doing time calculations and you want to display a total which is more than 24 hours, format as [hh]:mm:ss, not hh:mm:ss -- David Biddulph "Sean" wrote in message ... Hello, I am trying to take the date in the format of mm/dd/yyyy hh/mm/ss and on another worksheet creat a summary which would have a date field, a seperate start time field, a seperate end time field, and a total time field. Any suggestions as to the formulas that might help me out? Thanks |
Thread Tools | |
Display Modes | |
|
|