Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help formatting a cell.
Can such a request be done using Excel?
I need to enter a time in Cell B18 of 2325 or 11:25pm from that time i need it to calculate that cell B17 is 15 minutes prior to B18 (2310 or 11:10pm) B16 is 20 minutes prior to B18 (2305 or 11:05pm) B15 is 45 minutes prior to B18 (2240 or 10:40pm) B14 is 1 hour prior to B18 (2225 or 10:25pm) B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm) and so one...... When i will need to change cell b18 to another time.... I need all my cells to still have the same minutes prior to be calculated and deducted. Sorry if questions is compliacted and confusing. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help formatting a cell.
You can use a formula like this on B17:
=IF($B$18100,TIME(INT($B$18/100),MOD($B$18,100)-10,0),TIME(HOUR($B$18),MINUTE($B$18)-10,0)) And then change the -10 in the minute part on the different cells, or use additional cells where you keep the differences and reference them instead of hardcoding the numbers. Hope this helps, Miguel. "Need Help." wrote: Can such a request be done using Excel? I need to enter a time in Cell B18 of 2325 or 11:25pm from that time i need it to calculate that cell B17 is 15 minutes prior to B18 (2310 or 11:10pm) B16 is 20 minutes prior to B18 (2305 or 11:05pm) B15 is 45 minutes prior to B18 (2240 or 10:40pm) B14 is 1 hour prior to B18 (2225 or 10:25pm) B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm) and so one...... When i will need to change cell b18 to another time.... I need all my cells to still have the same minutes prior to be calculated and deducted. Sorry if questions is compliacted and confusing. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help formatting a cell.
=B18-15/24/60
=B18-20/24/60 =B18-45/24/60 =B18-60/24/60 =B18-70/24/60 Format all cells like this h:mm Be careful you don't enter a time that isn't close to midnight (excel can't handle negative time). You might need to enter the first time as date and time like this 1/1/2000 12:30 AM the format I gave you ignores the date and will show it as 0:30 if you format it like this h:mm AM/PM it will show as 12:30 AM "Need Help." wrote: Can such a request be done using Excel? I need to enter a time in Cell B18 of 2325 or 11:25pm from that time i need it to calculate that cell B17 is 15 minutes prior to B18 (2310 or 11:10pm) B16 is 20 minutes prior to B18 (2305 or 11:05pm) B15 is 45 minutes prior to B18 (2240 or 10:40pm) B14 is 1 hour prior to B18 (2225 or 10:25pm) B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm) and so one...... When i will need to change cell b18 to another time.... I need all my cells to still have the same minutes prior to be calculated and deducted. Sorry if questions is compliacted and confusing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting if value in cell is found in a named range | Excel Worksheet Functions | |||
Cell Formatting Conditional On Other Cells Fill Color? | Excel Worksheet Functions | |||
Conditional Formatting For A Cell Other Than The One With The Form | Excel Worksheet Functions | |||
Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
xls worksheet formatting a single cell | Excel Discussion (Misc queries) |