| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting more than 24 hours from a Date/Time
I am working on a schedule and I need to subtract 70 hours from the event
time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting more than 24 hours from a Date/Time
=A1-TIME(70,,)
"KSCGerald" wrote: I am working on a schedule and I need to subtract 70 hours from the event time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting more than 24 hours from a Date/Time
=A2-B2/24, if your original date and time are in A2 and your 70 in B2.
-- David Biddulph "KSCGerald" wrote in message ... I am working on a schedule and I need to subtract 70 hours from the event time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting more than 24 hours from a Date/Time
=A1-(70/24)
It should give you 1/7/08 08:20:44, not 1/8/08 08:20:44 "Teethless mama" wrote: =A1-TIME(70,,) "KSCGerald" wrote: I am working on a schedule and I need to subtract 70 hours from the event time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting more than 24 hours from a Date/Time
Isn't that what the OP had already done? Doesn't that suffer from the fact
that it wraps round at 24 hours, hence the OP's comment about 70 hours being treated as 22 hours, i.e. =MOD(70,24)? Hence the need to use =A1-70/24 -- David Biddulph "Teethless mama" wrote in message ... =A1-TIME(70,,) "KSCGerald" wrote: I am working on a schedule and I need to subtract 70 hours from the event time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting more than 24 hours from a Date/Time
Thanks Mama, that works!
"Teethless mama" wrote: =A1-(70/24) It should give you 1/7/08 08:20:44, not 1/8/08 08:20:44 "Teethless mama" wrote: =A1-TIME(70,,) "KSCGerald" wrote: I am working on a schedule and I need to subtract 70 hours from the event time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting more than 24 hours from a Date/Time
Thanks David, that works!
"David Biddulph" wrote: =A2-B2/24, if your original date and time are in A2 and your 70 in B2. -- David Biddulph "KSCGerald" wrote in message ... I am working on a schedule and I need to subtract 70 hours from the event time. If the event time is 1/10/08 06:20:44, when I subtract 70 hours, it only subtracts 22 hours with the TIME function, hence calculating 1/9/08 08:20:44. Is there some way to get Excel to automatically subtract the 70 hours to get the actual date and time (1/08/08 08:20:44)? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtracting Date/Time | Excel Discussion (Misc queries) | |||
| adding/subtracting # of hours from a time | Excel Discussion (Misc queries) | |||
| Subtracting hours from time | Excel Discussion (Misc queries) | |||
| Subtracting 2 hours from time | Excel Discussion (Misc queries) | |||
| Subtracting Cells formatted with Date AND Time to get hours? | Excel Discussion (Misc queries) | |||
Linear Mode
