Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Dear all,
If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
You need to have a date/time in A1 - for example, enter 8/01/07 13:00
in this cell and use Format | Cells | Number (tab) | Custom and set the format as ddd hh:mm. Then in A2 enter this formula: =A1 + 0.5 The format should be the same as A1. Excel treats times as fractions of a 24-hour day, which is why you need to add 0.5. Hope this helps. Pete Alexander wrote: Dear all, If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Hi Alexander
Excel stores time as fractions of a day, so 12 hours = 0.5. So in A2 =A1+0.5 If you were wanting to add times that were not as simple as that, e.g. 5 hours, then =A1+5/24 or =A1+TIME(5,0,0) -- Regards Roger Govier "Alexander" wrote in message ... Dear all, If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Thanks for the prompt reply. But this is going to be a repeated event.
Therefore, I cannot set a default timing at the very begining. So, If I just want the add on the basis of ddd hh:mm, is that possible? "Pete_UK" wrote: You need to have a date/time in A1 - for example, enter 8/01/07 13:00 in this cell and use Format | Cells | Number (tab) | Custom and set the format as ddd hh:mm. Then in A2 enter this formula: =A1 + 0.5 The format should be the same as A1. Excel treats times as fractions of a 24-hour day, which is why you need to add 0.5. Hope this helps. Pete Alexander wrote: Dear all, If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Yes, but what if I want the weekday change after time has been accumulated
over 24 hrs? Any chances? "Roger Govier" wrote: Hi Alexander Excel stores time as fractions of a day, so 12 hours = 0.5. So in A2 =A1+0.5 If you were wanting to add times that were not as simple as that, e.g. 5 hours, then =A1+5/24 or =A1+TIME(5,0,0) -- Regards Roger Govier "Alexander" wrote in message ... Dear all, If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Hallo
I have a quistion about time formats in exel too. If I f.ex have the time 1 minut and 31 seconds (tt:mm:ss) = 0:01:31 but i want the time only in seconds (ss) = 91. How do I do that? I hope you can help me! Greetings Mikkel "Alexander" wrote: Yes, but what if I want the weekday change after time has been accumulated over 24 hrs? Any chances? "Roger Govier" wrote: Hi Alexander Excel stores time as fractions of a day, so 12 hours = 0.5. So in A2 =A1+0.5 If you were wanting to add times that were not as simple as that, e.g. 5 hours, then =A1+5/24 or =A1+TIME(5,0,0) -- Regards Roger Govier "Alexander" wrote in message ... Dear all, If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Format as [s].
If you want to use the number 91 in a subsequent calculation, then use the formula =A1*24*3600 (and format as general or number, not time). -- David Biddulph "Mikkel" wrote in message ... Hallo I have a quistion about time formats in exel too. If I f.ex have the time 1 minut and 31 seconds (tt:mm:ss) = 0:01:31 but i want the time only in seconds (ss) = 91. How do I do that? I hope you can help me! Greetings Mikkel |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekday + Time Calculation in Cell
Hi Alexander
Did you try it? The date alters as well as the time. If you are adding more than 24 hours then =A1+50/24 would be the same as adding 2 days and 2 hours. -- Regards Roger Govier "Alexander" wrote in message ... Yes, but what if I want the weekday change after time has been accumulated over 24 hrs? Any chances? "Roger Govier" wrote: Hi Alexander Excel stores time as fractions of a day, so 12 hours = 0.5. So in A2 =A1+0.5 If you were wanting to add times that were not as simple as that, e.g. 5 hours, then =A1+5/24 or =A1+TIME(5,0,0) -- Regards Roger Govier "Alexander" wrote in message ... Dear all, If A1 is equal to Mon 1300, and when I plus another 12hrs in A2, it will become Tue 0100 in A2. Any idea how to achieve this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic time conversion in the SAME cell | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
multiplycation of money cell and time cell | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |